Database Refresh and User Permissions

How often do you refresh the databases from production to development?
How often do you miss to capture the users and their permission on the existing database before you restore?

Restoring or refreshing databases from one environment to another is a regular activity that a DBA would perform as part of their job. And as part of the refresh activity it is very important to make note of the users and their permissions before proceeding with the restoration of database. Also it is equally important to apply the correct permission after the restore.

Get SQL Server Database details using T-SQL

Here is a T-SQL script which gives you the details of all the databases in an SQL Server Instance.
This will be very useful when you are gathering SQL Server information from multiple servers

SET NOCOUNT ON
IF OBJECT_ID('tempdb..#DatabaseDetails') IS NOT NULL DROP TABLE #DatabaseDetails
CREATE TABLE #DatabaseDetails (
  DatabaseID int
, DatabaseName varchar(256)
, CreateDate datetime
, Collation varchar(256)
, ComparisonStyle int
, IsAnsiNullDefault bit
, IsAnsiNullsEnabled bit
, IsAnsiPaddingEnabled bit
, IsAnsiWarningsEnabled bit
, IsArithmeticAbortEnabled bit
, IsAutoClose bit
, IsAutoCreateStatistics bit
, IsAutoShrink bit
, IsAutoUpdateStatistics bit
, IsCloseCursorsOnCommitEnabled bit
, IsFulltextEnabled bit
, [IsInStandBy] bit
, IsLocalCursorsDefault bit
, IsMergePublished bit
, IsMergeSubscribed bit
, IsNullConcat bit
, IsNumericRoundAbortEnabled bit
, IsParameterizationForced bit
, [IsQuotedIdentifiersEnabled] bit
, IsPublished bit
, IsRecursiveTriggersEnabled bit
, IsSubscribed bit
, IsSyncWithBackup bit
, IsTornPageDetectionEnabled bit
, LCID int
, [Recovery] varchar(256)
, [SQLSortOrder] tinyint
, [Status] varchar(256)
, Updateability varchar(256)
, UserAccess varchar(256)
, [Version] int
, LastDatabaseBackup datetime
, LastIncremetalBackup datetime
, LastLogBackup datetime
, TotalLogSize bigint
, LogPercentUsed int
, [TotalDBSize_MB] bigint
, [cmptlevel] int
)

INSERT INTO #DatabaseDetails(
  DatabaseID
, [DatabaseName]
, [CreateDate]
, [Collation]
, [ComparisonStyle]
, [IsAnsiNullDefault]
, [IsAnsiNullsEnabled]
, [IsAnsiPaddingEnabled]
, [IsAnsiWarningsEnabled]
, [IsArithmeticAbortEnabled]
, [IsAutoClose]
, [IsAutoCreateStatistics]
, [IsAutoShrink]
, [IsAutoUpdateStatistics]
, [IsCloseCursorsOnCommitEnabled]
, [IsFulltextEnabled]
, [IsInStandBy]
, [IsLocalCursorsDefault]
, [IsMergePublished]
, [IsMergeSubscribed]
, [IsNullConcat]
, [IsNumericRoundAbortEnabled]
, [IsParameterizationForced]
, [IsQuotedIdentifiersEnabled]
, [IsPublished]
, [IsRecursiveTriggersEnabled]
, [IsSubscribed]
, [IsSyncWithBackup]
, [IsTornPageDetectionEnabled]
, [LCID]
, [Recovery]
, [SQLSortOrder]
, [Status]
, [Updateability]
, [UserAccess]
, [Version]
, [cmptlevel])
SELECT sd.dbid as 'DatabaseID'
 , sd.[name] as 'DatabaseName'
 , sd.crdate as 'CreateDate'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Collation') as varchar(256)) as 'Collation'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'ComparisonStyle') as varchar(256)) as 'ComparisonStyle'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiNullDefault') as bit) as 'IsAnsiNullDefault'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiNullsEnabled') as bit) as 'IsAnsiNullsEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiPaddingEnabled') as bit) as 'IsAnsiPaddingEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiWarningsEnabled') as bit) as 'IsAnsiWarningsEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsArithmeticAbortEnabled') as bit) as 'IsArithmeticAbortEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoClose') as bit) as 'IsAutoClose'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoCreateStatistics') as bit) as 'IsAutoCreateStatistics'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoShrink') as bit) as 'IsAutoShrink'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoUpdateStatistics') as bit) as 'IsAutoUpdateStatistics'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsCloseCursorsOnCommitEnabled') as bit) as 'IsCloseCursorsOnCommitEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsFulltextEnabled') as bit) as 'IsFulltextEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsInStandBy') as bit) as 'IsInStandBy'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsLocalCursorsDefault') as bit) as 'IsLocalCursorsDefault'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsMergePublished') as bit) as 'IsMergePublished'
 , CASE WHEN sd.category & 8 = 8 THEN 1 ELSE 0 end as 'IsMergeSubscribed'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsNullConcat') as bit) as 'IsNullConcat'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsNumericRoundAbortEnabled') as bit) as 'IsNumericRoundAbortEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsParameterizationForced') as bit) as 'IsParameterizationForced'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsQuotedIdentifiersEnabled') as bit) as 'IsQuotedIdentifiersEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsPublished') as bit) as 'IsPublished'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsRecursiveTriggersEnabled') as bit) as 'IsRecursiveTriggersEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsSubscribed') as bit) as 'IsSubscribed'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsSyncWithBackup') as bit) as 'IsSyncWithBackup'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsTornPageDetectionEnabled') as bit) as 'IsTornPageDetectionEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'LCID') as int) as 'LCID'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Recovery') as varchar(256)) as 'Recovery'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'SQLSortOrder') as tinyint) as 'SQLSortOrder'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Status') as varchar(256)) as 'Status'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Updateability') as varchar(256)) as 'Updateability'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'UserAccess') as varchar(256)) as 'UserAccess'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Version') as int) as 'Version'
 , cmptlevel
FROM master.dbo.sysdatabases sd 
ORDER BY sd.dbid



UPDATE dbd
SET   LastDatabaseBackup = fullbak.LastDatabaseBackup
 , LastIncremetalBackup = incbak.LastIncremetalBackup
 , LastLogBackup = logbak.LastLogBackup
FROM #DatabaseDetails dbd
left join (
 SELECT sd.dbid
   , sd.[name] as 'DatabaseName'
   , max(t1.backup_finish_date) as 'LastDatabaseBackup'
 FROM master.dbo.sysdatabases sd 
 join msdb.dbo.backupset t1  on t1.type = 'D' and t1.database_name = sd.[name]
 GROUP BY sd.dbid, sd.[name]
) fullbak ON fullbak.dbid = dbd.DatabaseID
left join (
 SELECT sd.dbid
   , sd.[name] as 'DatabaseName'
   , max(t2.backup_finish_date) as 'LastIncremetalBackup'
 FROM master.dbo.sysdatabases sd 
 join msdb.dbo.backupset t2  on t2.type = 'I' and t2.database_name = sd.[name]
 GROUP BY sd.dbid, sd.[name]
) incbak on incbak.dbid = dbd.DatabaseID
left join (
 SELECT sd.dbid
   , sd.[name] as 'DatabaseName'
   , max(t3.backup_finish_date) as 'LastLogBackup'
 FROM master.dbo.sysdatabases sd 
 join msdb.dbo.backupset t3  on t3.type = 'L' and t3.database_name = sd.[name]
 GROUP BY sd.dbid, sd.[name]
) logbak on logbak.dbid = dbd.DatabaseID


IF OBJECT_ID('tempdb..#logspace') IS NOT NULL DROP TABLE #logspace
CREATE TABLE #logspace(
   DatabaseName varchar(256)
 , TotalLogSize decimal(20,4)
 , PercentUsed decimal(20,4)
 , [Status] varchar(50)
)
INSERT INTO #logspace
EXEC('DBCC sqlperf(logspace)')

UPDATE dbd
SET   TotalLogSize = ls.TotalLogSize
 , LogPercentUsed = Convert(int, ls.PercentUsed)
FROM #DatabaseDetails dbd
join #logspace ls ON dbd.DatabaseID = db_id(ls.DatabaseName)

EXEC master.dbo.sp_MSForEachDB 'update #DatabaseDetails
set TotalDBSize_MB = (select (sum([size]) * 8.0) / 1024.0 
FROM [?].[dbo].[sysfiles] )
where DatabaseID = db_id(''?'')'

SELECT DatabaseID
, DatabaseName
, [TotalDBSize_MB]
, TotalLogSize
, LogPercentUsed
, CreateDate
, [Status]
, LastDatabaseBackup
, LastIncremetalBackup
, LastLogBackup
, [Recovery]
, [Updateability]
, [UserAccess]
, [Collation]
, [ComparisonStyle]
, [LCID]
, [SQLSortOrder]
, [Version]
, [cmptlevel]
, [IsAutoUpdateStatistics]
, [IsAutoCreateStatistics]
, [IsInStandBy]
, [IsAutoShrink]
, [IsNullConcat]
, [IsFulltextEnabled]
, [IsPublished]
, [IsSubscribed]
, [IsMergePublished]
, [IsMergeSubscribed]
, [IsAnsiNullDefault]
, [IsAnsiNullsEnabled]
, [IsAnsiPaddingEnabled]
, [IsAnsiWarningsEnabled]
, [IsArithmeticAbortEnabled]
, [IsAutoClose]
, [IsCloseCursorsOnCommitEnabled]
, [IsLocalCursorsDefault]
, [IsNumericRoundAbortEnabled]
, [IsParameterizationForced]
, [IsQuotedIdentifiersEnabled]
, [IsRecursiveTriggersEnabled]
, [IsSyncWithBackup]
, [IsTornPageDetectionEnabled]
FROM #DatabaseDetails
ORDER BY DatabaseName
SET NOCOUNT OFF

Get SQL Server details using T-SQL

Here is a T-SQL script which gives you the details of a SQL Server.
This will be very useful when you are gathering SQL Server information from multiple servers and works for SQL Server 2005 and above.

CREATE TABLE #ServerDetails(ID int,  Name  sysname, Internal_Value int, Value nvarchar(512))
INSERT #ServerDetails EXEC master.dbo.xp_msver

DECLARE @InstanceName nvarchar(50)
DECLARE @value VARCHAR(100)
DECLARE @RegKey_InstanceName nvarchar(500)
DECLARE @RegKey nvarchar(500)
DECLARE @AuditLevel int
DECLARE @DataDirectory nvarchar(500)
DECLARE @LogDirectory nvarchar(500)
DECLARE @BackupDirectory nvarchar(500)

SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),
'MSSQLSERVER'))

if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8
BEGIN
SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey_InstanceName,
  @value_name = @InstanceName,
  @value = @value OUTPUT

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\'

    
EXEC master..xp_regread 
  @rootkey='HKEY_LOCAL_MACHINE',
  @key=@RegKey,
  @value_name='AuditLevel',
  @value=@AuditLevel OUTPUT

EXEC master..xp_regread 
  @rootkey='HKEY_LOCAL_MACHINE',
  @key=@RegKey,
  @value_name='DefaultData',
  @value=@DataDirectory OUTPUT

EXEC master..xp_regread 
  @rootkey='HKEY_LOCAL_MACHINE',
  @key=@RegKey,
  @value_name='DefaultLog',
  @value=@LogDirectory OUTPUT

EXEC master..xp_regread 
  @rootkey='HKEY_LOCAL_MACHINE',
  @key=@RegKey,
  @value_name='BackupDirectory',
  @value=@BackupDirectory OUTPUT

END

SELECT  SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [Machine Name]
    ,SERVERPROPERTY('ServerName') AS [SQL Server Name]
    ,SERVERPROPERTY('InstanceName') AS [Instance Name]
    ,SERVERPROPERTY('Collation') AS [Server Collation]
    ,'Microsoft SQL Server ' + CAST(SERVERPROPERTY('Edition') AS varchar(250)) AS Edition
    ,SERVERPROPERTY('ProductLevel') AS [Product Level]
    ,(SELECT Value FROM #ServerDetails WHERE Name = N'Language') AS [Language]
    ,(SELECT Value FROM #ServerDetails WHERE Name = N'Platform') AS [Platform]
    ,(SELECT 'Microsoft Windows NT ' + Value from #ServerDetails where Name = N'WindowsVersion') AS [Operating System]
    ,(SELECT Internal_Value FROM #ServerDetails WHERE Name = N'ProcessorCount') AS [Processors]
    ,(SELECT CAST(Internal_Value AS varchar)+ ' (MB)' FROM #ServerDetails WHERE Name = N'PhysicalMemory') AS Memory
    , CASE WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'True' ELSE 'False' END AS IsClustered
    ,(SELECT value from sys.configurations where name='min server memory (MB)') AS 'Min Server Memory (MB)'
    ,(SELECT value from sys.configurations where name='max server memory (MB)') AS 'Max Server Memory (MB)'
    ,(SELECT CASE WHEN value=0 THEN 'True' ELSE 'False' END from sys.configurations where name='affinity mask') AS 'Automatically set processor affinity mask for all processor'
    ,(SELECT CASE WHEN value=0 THEN 'True' ELSE 'False' END from sys.configurations where name='affinity I/O mask') AS 'Automatically set I/O affinity mask for all processor'
    ,CASE WHEN SERVERPROPERTY('IsIntegratedSecurityOnly')= 1 THEN 'Windows Authentication Mode' 
    WHEN SERVERPROPERTY('IsIntegratedSecurityOnly')= 0 THEN 'SQL Server and Windows Authentication Mode' END AS [Server Authentication]
    ,CASE WHEN @AuditLevel = 0 THEN 'None' 
    WHEN @AuditLevel = 1 THEN 'Successful Logins Only'
    WHEN @AuditLevel = 2 THEN 'Failed Logins Only'
    WHEN @AuditLevel = 3 THEN 'Both Failed and Successful Logins'
    END AS [Audit Level]
    ,(select CASE WHEN value = 0 THEN 'False' WHEN value = 1 THEN 'True' END from sys.configurations where name='remote access') AS 'Allow remote connections to this Server'
    ,(select CASE WHEN value = 0 THEN 'unlimited' ELSE value END from sys.configurations where name='user connections') AS 'Max number of concurrent Connections'
    ,(select CASE WHEN value = 0 THEN 'No Timeout' ELSE value END from sys.configurations where name='remote query timeout (s)') AS 'Query Timeout (s)'
    ,(select CASE WHEN value = 0 THEN 'False' WHEN value = 1 THEN 'True' END from sys.configurations where name='remote access') AS 'Allow Remote Connections to this server'
    ,@DataDirectory AS 'Default Data Directory'
    ,@LogDirectory AS 'Default Log Directory'
    ,@BackupDirectory AS 'Default Backup Directory'
    ,(SELECT value from sys.configurations WHERE name='max degree of parallelism') AS 'Max Degree of Parallelism'
    ,(SELECT value from sys.configurations WHERE name='remote login timeout (s)') AS 'Remote Login Timeout (s)'
    ,(SELECT CASE WHEN value = 0 THEN 'False' WHEN value = 1 THEN 'True' END from sys.configurations WHERE name='scan for startup procs') AS 'Scan for Startup Procs'

DROP TABLE #ServerDetails

Different ways to check your SQL Server(s) Authentication mode

Checking the Authentication mode using T-SQL:

1. Using "xp_LoginConfig" extended Stored Procedure
   

   EXEC Master.dbo.xp_LoginConfig 'login mode'
   

   
   
2. Using "SERVERPROPERTY" Function
   

   SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   
   WHEN 1 THEN 'Windows Authentication mode'   
   WHEN 0 THEN 'SQL Server and Windows Authentication mode'   
   END as [Authentication Mode]  
   
   

3. Using Registry
   

   DECLARE @Mode INT  
   EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
   N'Software\Microsoft\MSSQLServer\MSSQLServer',   
   N'LoginMode', @Mode OUTPUT  
   
   SELECT CASE @Mode    
   WHEN 1 THEN 'Windows Authentication mode'
   WHEN 2 THEN 'SQL Server and Windows Authentication mode'
   ELSE 'Not known'
   END as [Authentication Mode]  
   

Checking the Authentication mode using SSMS:

To check the Authentication mode using SSMS,

1. Right-Click on the Server
2. Choose "Properties"
3. Navigate to "Security" Page
4. Check "Server Authentication" Section

Moving SQL Agent Log file "SQLAGENT.OUT" to a different location

In one of my previous posts "Undocumented stored procedure for retrieving SQL Agent properties", I had explained how to retrieve the SQL Agent Properties.

In this post I will explain how to change the location of the SQL Agent Log file "SQLAGENT.OUT".

To find the current location of SQLAGENT.OUT file, execute the below SP and look at the value of the column "errorlog_file". This is location where SQLAGENT.OUT file is located.

EXEC msdb..sp_get_sqlagent_properties 
GO

Output:

Now, to change the location of SQLAGENT.OUT file, run the below command and re-start the SQL Server Agent Service and you are done.

EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'<new path>\SQLAGENT.OUT' 
GO

T-SQL Query to change the datatype of multiple columns of single or multiple tables

There might be a situation where a person designed a database with a particular datatype for many tables and now you want to change the datatype to a different one for all those columns in a particular table or multiple tables due to various reasons.
Changing the datatype for a single table or five tables or 10 tables is a easy task, but when the tables list is in hundreds how easy is it do in the traditional way?
Below stored procedure gives you the flexibility of changing the datatype of multiple columns in a single or multiple tables at one go.

Things to note before running the scripts

1. Backup your database 
2. These scripts are provided AS IS without warranty of any kind.

Script:

CREATE PROC usp_ChangeColumnDatatype (@currentDataType nvarchar(25), 
   @DataTypeToSet nvarchar(50), 
   @ScanTables nvarchar(100), 
   @PrintCommandsOnly bit )

AS
SET NOCOUNT ON
DECLARE @ScanTables_Local nvarchar(100)
SET @ScanTables_Local = '''' + REPLACE(REPLACE(@ScanTables,',',''','''),' ','') + '''' 
IF @ScanTables = 'All'
BEGIN
CREATE TABLE #Temp (CommandsToExecute nvarchar(max))
INSERT INTO #temp SELECT 'ALTER TABLE ' + OBJECT_NAME(o.object_id) + 
    ' ALTER COLUMN ' + c.name + ' ' + @DataTypeToSet +
    CASE WHEN c.is_nullable = 0 THEN ' NOT NULL' ELSE ' NULL' END AS CommandsToExecute
FROM sys.objects o 
INNER JOIN sys.columns c ON o.object_id=c.object_id
INNER JOIN sys.types t ON c.system_type_id=t.system_type_id
WHERE o.type='u'
and t.name = @currentDataType
END

IF @ScanTables <> 'All'
BEGIN
CREATE TABLE #Temp_SpecificTables (CommandsToExecute nvarchar(max))

DECLARE @Cmd nvarchar(max)
SET @Cmd = 'INSERT INTO #Temp_SpecificTables  SELECT ''ALTER TABLE '' + OBJECT_NAME(o.object_id) + 
    '' ALTER COLUMN '' + c.name + ''' + @DataTypeToSet + ''' +
    CASE WHEN c.is_nullable = 0 THEN ''NOT NULL'' ELSE ''NULL'' END AS CommandsToExecute
FROM sys.objects o 
INNER JOIN sys.columns c ON o.object_id=c.object_id
INNER JOIN sys.types t ON c.system_type_id=t.system_type_id
WHERE o.type=''u''
and t.name = '''+@currentDataType+''' and OBJECT_NAME(o.object_id) in ('+ @ScanTables_Local + ')'
--PRINT @cmd
EXECUTE (@cmd)
END
if @PrintCommandsOnly = 'True' and @ScanTables = 'All'
BEGIN
SELECT * FROM #Temp
DROP TABLE #Temp
END

if @PrintCommandsOnly = 'False' and @ScanTables = 'All'
BEGIN
--SELECT * FROM #Temp
PRINT 'Changing of the datatypes of table(s) '+ @ScanTables +' from ' + @currentDataType + ' to '+ @DataTypeToSet + ' started at ' + CAST(GETDATE() AS varchar)
WHILE (SELECT COUNT(*) FROM #Temp) <> 0
BEGIN
DECLARE @varTemp nvarchar(max)
SELECT @varTemp = CommandsToExecute FROM #Temp
EXECUTE (@varTemp)
DELETE FROM #temp WHERE CommandsToExecute = @varTemp
END
DROP TABLE #Temp
PRINT 'Changing of the datatypes of table(s) '+ @ScanTables +' from ' + @currentDataType + ' to '+ @DataTypeToSet + ' ended at ' + CAST(GETDATE() AS varchar)
END

if @PrintCommandsOnly = 'True' and @ScanTables <> 'All'
BEGIN
SELECT * FROM #Temp_SpecificTables
DROP TABLE #Temp_SpecificTables
END

if @PrintCommandsOnly = 'False' and @ScanTables <> 'All'
BEGIN
--SELECT * FROM #Temp_SpecificTables
PRINT 'Changing of the datatypes of table(s) '+ @ScanTables_Local +' from ' + @currentDataType + ' to '+ @DataTypeToSet + ' started at ' + CAST(GETDATE() AS varchar)
WHILE (SELECT COUNT(*) FROM #Temp_SpecificTables) <> 0
BEGIN
DECLARE @varTemp_SpecificTables nvarchar(max)
SELECT @varTemp_SpecificTables = CommandsToExecute FROM #Temp_SpecificTables
EXECUTE (@varTemp_SpecificTables)
DELETE FROM #Temp_SpecificTables WHERE CommandsToExecute = @varTemp_SpecificTables
END
DROP TABLE #Temp_SpecificTables
PRINT 'Changing of the datatypes of table(s) '+ @ScanTables_Local +' from ' + @currentDataType + ' to '+ @DataTypeToSet + ' ended at ' + CAST(GETDATE() AS varchar)
END
SET NOCOUNT OFF
GO

Usage:

EXEC usp_ChangeColumnDatatype @currentDataType = 'nvarchar', 
         @DataTypeToSet = 'varchar(50)', 
         @ScanTables = 'Table_1,Table_2', --Table1, Table2,Table3 or ALL
         @PrintCommandsOnly = 'FALSE' -- TRUE - Will print the commands or FALSE - Will execute the commands.

Granting Read-Only and administrative access to Central Management Server (CMS)

In one of my previous post, I had discussed about the Central Management Server and how to Register it.
In this post, I will be telling how to give read-only access and a Administrative access to an existing CMS.

Grating the Read-Only and Administrative access to CMS is very simply and the activity includes adding the right database users to right groups in msdb database.

Granting Read-Only access:

USE [msdb]
GO
CREATE USER [DBUserName] FOR LOGIN [LoginName]
GO
EXEC sp_addrolemember N'ServerGroupReaderRole', N'DBUserName'
GO

Granting Administrator access:
This access is usually give to the DBA's

USE [msdb]
GO
CREATE USER [DBAUserName] FOR LOGIN [DBALoginName]
GO
EXEC sp_addrolemember N'ServerGroupAdministratorRole', N'DBAUserName'
GO

Give access to a non sysadmin user to run Profiler

Sometimes as a DBA, you come across a situation where you need to give access to a non sysadmin user to run profiler on a particular SQL server.
If you try to run the profiler using the user who does not have sysadmin access then you will get the below error.

Here is the solution how to grant access to a non sysadmin user to run profiler.
Using Query: 

-- To Grant access to a Windows Login
USE master;
GRANT ALTER TRACE TO [Domain\WindowsLogin]

-- To Grant access to a SQL Login
USE master;
GRANT ALTER TRACE TO [SQL User]

Using SSMS:

1. Expand the Server in object Explorer
2. Expand "Security" folder and then "logins"
3. Right-Click on the login to which you need to give access and then go to "Properties" of that login
4. Go to "Securables" Tab
5. Select the server you want to add the permission
6. In the "Permission for <Server Name>" block, click on "Grant" check box for "Alter Trace" and click "OK"
7. Once this is completed, the permission should appear in the "Effective" Tab

Note: The Granter should be a sysadmin user.  

Convert Rows to Column using COALESCE() function

Using the below query we can convert the rows to column sperated by a delimiter.
In the query I am using ';' as the delimiter and you can change the delimiter of your choice by replacing ';'.
Data from Table:

Query:

Use AdventureWorks2008R2
GO
DECLARE @eMailList nvarchar(max) 

SELECT @eMailList = COALESCE(@eMailList + ';', '') + 
   CAST(eMail AS nvarchar(max))
FROM Employees

Select @eMailList as eMailList

Output of the above Query:

Get last backup details of all databases in a server

This stored procedure give you the information about latest backups happened on all databases in a server.
This SP works for SQL server 2005 and up.

Create Proc sp_BackupDetails AS DECLARE @BackupDetails table ([Server Name] nvarchar(500), [Database Name] nvarchar(500), [Last Full Backup] nvarchar(500), [Last Differential Backup] nvarchar(500), [Last Log Backup] nvarchar(500), [Last File or filegroup Backup] nvarchar(500), [Last Differential file Backup] nvarchar(500), [Last Partial Backup] nvarchar(500), [Last Differential Partial Backup] nvarchar(500) ) DECLARE @DBName nvarchar(500) Declare DBName Cursor for Select name from sys.databases Open DBName Fetch Next from DBName into @DBName While @@fetch_status = 0 BEGIN Insert into @BackupDetails select @@ServerName as [Server Name]        ,SDB.name AS [Database Name]        ,(select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='D') AS [Last Full Backup]        ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='I') AS [Last Differential Backup]        ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='L') AS [Last Log Backup]        ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='F') AS [Last File or filegroup Backup]        ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='G') AS [Last Differential file Backup]        ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='P') AS [Last Partial Backup]        ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='Q') AS [Last Differential Partial Backup] from sys.databases SDB where SDB.name =@DBName Fetch Next from DBName into @DBName END Close DBName DEALLOCATE DBName Select * from @BackupDetails GO

Usage:

Exec sp_BackupDetails

go

Results:

Finding space usage of database files

DBA's are required to watch the space usage of database files in order to take preventive measures of future failures with respect to database full issues.
This will be usually required when there is a bulk activity happening on a database.
To do this make use of the below stored procedure.
Create this SP in a database and execute it in regular intervals to get the latest status of the database files.
This can be used to view the space usage of all the database files or for a particular threshold value.

Use master GO Create proc sp_SpaceUsageReport (@Threshold int=80) as CREATE TABLE tempdb..SpaceUsage (   DatabaseName nvarchar(100)  ,LogicalFileName nvarchar(500)  ,FileType nvarchar(10)  ,PhysicalFileLocation nvarchar(500)  ,[FileSize (MB)] float  ,[SpaceUsed (MB)] float  ,[FreeSpace (MB)] float  ,[% Used] AS 100-(([FileSize (MB)]-[SpaceUsed (MB)])/[FileSize (MB)])*(100)  ,[% Free]  AS (([FileSize (MB)]-[SpaceUsed (MB)])/[FileSize (MB)])*(100) ) If (SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion')))) = 8 BEGIN EXEC sp_MSforeachdb 'USE [?]; INSERT INTO tempdb..SpaceUsage(DatabaseName            ,LogicalFileName            ,FileType            ,PhysicalFileLocation               ,[FileSize (MB)]            ,[SpaceUsed (MB)]            ,[FreeSpace (MB)]) SELECT DB_NAME() AS DatabaseName        ,name AS LogicalFileName        ,FileType = CASE WHEN FILEPROPERTY(name,''IsLogFile'')=0 THEN ''Data File'' WHEN FILEPROPERTY(name,''IsLogFile'')=1 THEN ''Log File'' END        ,filename AS PhysicalFileLocation        ,CONVERT(float,ROUND(size/128.000,2)) AS [FileSize (MB)]        ,CONVERT(float,ROUND(FILEPROPERTY(name,''SpaceUsed'')/128.000,2)) AS [SpaceUsed (MB)]        ,CONVERT(float,ROUND((size-FILEPROPERTY(name,''SpaceUsed''))/128.000,2)) AS [FreeSpace (MB)] FROM dbo.sysfiles ORDER BY FileType ' END If (SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<> 8 BEGIN EXEC sp_MSforeachdb 'USE [?]; INSERT INTO tempdb..SpaceUsage(DatabaseName            ,LogicalFileName            ,FileType            ,PhysicalFileLocation            ,[FileSize (MB)]            ,[SpaceUsed (MB)]            ,[FreeSpace (MB)]) SELECT DB_NAME() AS DatabaseName             ,name AS LogicalFileName             ,FileType = CASE WHEN type_desc =''Rows'' THEN ''Data File'' WHEN type_desc =''LOG'' THEN ''Log File'' END             ,physical_name AS PhysicalFileLocation             ,CONVERT(float,ROUND(size/128.000,2)) AS [FileSize (MB)]             ,CONVERT(float,ROUND(FILEPROPERTY(name,''SpaceUsed'')/128.000,2)) AS [SpaceUsed (MB)]             ,CONVERT(float,ROUND((size-FILEPROPERTY(name,''SpaceUsed''))/128.000,2)) AS [FreeSpace (MB)] FROM sys.database_files ORDER BY FileType' END if (Select COUNT(*) from tempdb..SpaceUsage where [% Used]>@Threshold)>0 Begin /* -- Enable this Content if you want to send email. DECLARE @table  NVARCHAR(MAX),@Subject Nvarchar(500) ; Set @Subject='[SQLAlert] Database Files Space Threshold exceeded Report from ' + CAST(@@SERVERNAME as nvarchar) SET @table =     N'<H1>Threshold Value for this Report is '+CAST(@Threshold AS nvarchar)+' Percentage. </H1>' +     N'<table border="1">' +     N'<tr><th>DatabaseName</th><th>FileType</th><th>PhysicalFileLocation</th><th>FileSize (MB)</th><th>% Used</th></tr> ' +     CAST ( ( Select td=DatabaseName, '',td=FileType, '',td=PhysicalFileLocation,'',td=CAST([FileSize (MB)] as nvarchar),'',td=CAST([% Used] AS nvarchar) from tempdb..SpaceUsage where [% Used]>@Threshold               FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) )    +     N'</table>' ; EXEC msdb.dbo.sp_send_dbmail @profile_name='DatabaseMail', --Change to your Profile Name       @recipients='sandeshsegu@SansSQL.com', --Put the email address of those who want to receive the e-mail     @subject = @Subject,     @body = @table,     @body_format = 'HTML' ;   */    select * from  tempdb..SpaceUsage where [% Used]>@Threshold                                  End DROP TABLE tempdb..SpaceUsage

To send an email of this report you need to

1. Configure Database Mail option. To configure Database mail option, follow this post.
2. Uncomment the below content in the SP.

/* -- Enable this Content if you want to send email. DECLARE @table  NVARCHAR(MAX),@Subject Nvarchar(500) ; Set @Subject='[SQLAlert] Database Files Space Threshold exceeded Report from ' + CAST(@@SERVERNAME as nvarchar) SET @table =     N'<H1>Threshold Value for this Report is '+CAST(@Threshold AS nvarchar)+' Percentage. </H1>' +     N'<table border="1">' +     N'<tr><th>DatabaseName</th><th>FileType</th><th>PhysicalFileLocation</th><th>FileSize (MB)</th><th>% Used</th></tr> ' +     CAST ( ( Select td=DatabaseName, '',td=FileType, '',td=PhysicalFileLocation,'',td=CAST([FileSize (MB)] as nvarchar),'',td=CAST([% Used] AS nvarchar) from tempdb..SpaceUsage where [% Used]>@Threshold               FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) )    +     N'</table>' ; EXEC msdb.dbo.sp_send_dbmail @profile_name='DatabaseMail', --Change to your Profile Name       @recipients='sandeshsegu@SansSQL.com', --Put the email address of those who want to receive the e-mail     @subject = @Subject,     @body = @table,     @body_format = 'HTML' ;   */

Usage of this SP:
This SP expects a parameter called @Threshold
If you specify the @Threshold=0 then, this SP will give space usage details of all the database files.

Exec sp_SpaceUsageReport @Threshold=0

If you specify the @Threshold=80 then, this SP will give space usage details of those database files which exceeds the threshold 80 percent.

Exec sp_SpaceUsageReport @Threshold=80

T-SQL Query to find the list of Indexed Views in a Database

Here is the query which can be used for getting the list of Indexed views in a Database.

USE <DatabaseName>

GO

SELECT * FROM sys.objects

WHERE type='V'

and OBJECTPROPERTY(object_id,'IsIndexed')=1

T-SQL Query to find the SQL Server Protocols Status

Here is a T-SQL Query that I have developed to get the status of the SQL Server Protocols.
This query reads data from the registry.

DECLARE @InstanceName nvarchar(50) DECLARE @value VARCHAR(100) DECLARE @value_Out VARCHAR(100) DECLARE @RegKey_InstanceName nvarchar(500) DECLARE @RegKey nvarchar(500) SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'), 'MSSQLSERVER')) CREATE TABLE #SQLServerProtocols (ProtocolName nvarchar(25), Value nvarchar(10), Data bit) if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8 BEGIN SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL' EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey_InstanceName,   @value_name = @InstanceName,   @value = @value OUTPUT SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Sm' Insert into #SQLServerProtocols (Value,Data) EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'Enabled' EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'DisplayName',   @value = @value_Out OUTPUT UPDATE #SQLServerProtocols set ProtocolName=@value_Out  where ProtocolName is null SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Np' Insert into #SQLServerProtocols (Value,Data) EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'Enabled' EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'DisplayName',   @value = @value_Out OUTPUT UPDATE #SQLServerProtocols set ProtocolName=@value_Out where ProtocolName is null SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\TCP' Insert into #SQLServerProtocols (Value,Data) EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'Enabled' EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'DisplayName',   @value = @value_Out OUTPUT UPDATE #SQLServerProtocols set ProtocolName=@value_Out  where ProtocolName is null SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Via' Insert into #SQLServerProtocols (Value,Data) EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'Enabled' EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'DisplayName',   @value = @value_Out OUTPUT UPDATE #SQLServerProtocols set ProtocolName=@value_Out  where ProtocolName is null END SELECT ProtocolName, IsEnabled=CASE WHEN Data=1 THEN 'Enabled'  ELSE 'Disabled' END FROM #SQLServerProtocols DROP TABLE #SQLServerProtocols

Download this script from here.