Important Scripts for MS SQL DBA

Unveil hidden performance bottlenecks! Script exposes top resource-intensive queries & fragmentation levels. Ideal for DB health checks.

Important Scripts for MS SQL DBA

Important Scripts for MS SQL DBA

NOTE: Please test these scripts in the Non-prod environment first, after testing you can use them on the production server. (Updated)

Long-running query script in MS SQL Server

SELECT DISTINCT TOP 20
est.TEXT AS QUERY ,
Db_name(dbid),schema

eqs.execution_count AS EXEC_CNT,
eqs.max_elapsed_time AS MAX_ELAPSED_TIME,
ISNULL(eqs.total_elapsed_time / NULLIF(eqs.execution_count,0), 0) AS AVG_ELAPSED_TIME,
eqs.creation_time AS CREATION_TIME,
ISNULL(eqs.execution_count / NULLIF(DATEDIFF(s, eqs.creation_time, GETDATE()),0), 0) AS EXEC_PER_SECOND,
total_physical_reads AS AGG_PHYSICAL_READS
FROM sys.dm_exec_query_stats eqs
CROSS APPLY sys.dm_exec_sql_text( eqs.sql_handle ) est
ORDER BY
eqs.max_elapsed_time DESC


To check connected sessions to the particular DB script in MS SQL Server

SELECT DB_NAME(dbid) AS DBName,
COUNT(dbid) AS NumberOfConnections,
loginame
FROM sys.sysprocesses
GROUP BY dbid, loginame
ORDER BY DB_NAME(dbid)


Backup details using MSDB database script in MS SQL Server

SELECT database_name, backup_start_date, is_copy_only,
first_lsn as ‘LSN of full bakup’
FROM msdb..backupset
WHERE database_name = ‘CNA’
ORDER BY backup_start_date DESC
GO


Backup and restoration status script in MS SQL Server

SELECT r.session_id AS [Session_Id]
,r.command AS [command]
,CONVERT(NUMERIC(6, 2), r.percent_complete) AS [% Complete]
,GETDATE() AS [Current Time]
,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [Estimated Completion Time]
,CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [Estimated Min]
,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [Estimated Hours]
,CONVERT(VARCHAR(1000), (
SELECT SUBSTRING(TEXT, r.statement_start_offset / 2, CASE
WHEN r.statement_end_offset = – 1
THEN 1000
ELSE (r.statement_end_offset – r.statement_start_offset) / 2
END) ‘Statement text’
FROM sys.dm_exec_sql_text(sql_handle)
))
FROM sys.dm_exec_requests r
WHERE command like ‘RESTORE%’
or command like ‘BACKUP%’

(Important Scripts for MS SQL DBA)


Rebuild Index – Rebuild Index DB level script in MS SQL Server

DECLARE @Database NVARCHAR(255)
DECLARE @Table NVARCHAR(255)
DECLARE @cmd NVARCHAR(1000)
DECLARE DatabaseCursor CURSOR READ_ONLY FOR
SELECT name FROM master.sys.databases
WHERE name IN (‘providerportal’) — databases
AND state = 0 — database is online
AND is_in_standby = 0 — database is not read only for log shipping
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = ‘DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ”[” + table_catalog + ”].[” + table_schema + ”].[” +
table_name + ”]” as tableName FROM [‘ + @Database + ‘].INFORMATION_SCHEMA.TABLES WHERE table_type = ”BASE TABLE”’
— create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @cmd = ‘ALTER INDEX ALL ON ‘ + @Table + ‘ REBUILD’
PRINT @cmd — uncomment if you want to see commands
EXEC (@cmd)
END TRY
BEGIN CATCH
PRINT ‘—‘
PRINT @cmd
PRINT ERROR_MESSAGE()
PRINT ‘—‘
END CATCH
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor


Update database stats script in MS SQL Server

exec sp_updatestats


Change schema owner to dbo script in MS SQL Server

SELECT ‘ALTER SCHEMA dbo TRANSFER [‘ + SysSchemas.Name + ‘].[‘ + DbObjects.Name + ‘];’
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = ‘sysadm’
AND (DbObjects.Type IN (‘U’, ‘P’, ‘V’))


Execute access to the SP script in MS SQL Server

— Grant execute on sp

GRANT EXEC TO User_name


Forcefully shrink tempdb script in MS SQL Server

DBCC FREEPROCCACHE
GO
use tempdb
GO
— Shrink tempDB data file
DBCC SHRINKFILE (‘tempdev’ , 1 )
go
— Shrink tempdb log file
dbcc shrinkfile (‘templog’ ,1 )

(Important Scripts for MS SQL DBA)


User backup script in MS SQL Server

Use [DB_Name]
Go
Set NOCOUNT ON
Go
–Script 1
Print Convert(Varchar(50),’USE ‘+DB_Name())
SELECT ‘CREATE USER [‘ + NAME + ‘] FOR LOGIN [‘ + NAME + ‘]’ AS ‘–Database Users Creation–‘
FROM sys.database_principals
WHERE Type IN (
‘U’
,’S’
)
AND NAME NOT IN (
‘dbo’
,’guest’
,’sys’
,’INFORMATION_SCHEMA’
)

–Script 2
SELECT ‘EXEC sp_AddRoleMember ”’ + DBRole.NAME + ”’, ”’ + DBUser.NAME + ”” AS ‘–Add Users to Database Roles–‘
FROM sys.database_principals DBUser
INNER JOIN sys.database_role_members DBM ON DBM.member_principal_id = DBUser.principal_id
INNER JOIN sys.database_principals DBRole ON DBRole.principal_id = DBM.role_principal_id


(Important Scripts for MS SQL DBA)

Log File max size script in MS SQL Server

if object_id(‘tempdb..#dbsize’) is not null
drop table #dbsize;
GO
create table #dbsize (database_name sysname,
currrent_size int,
max_size int,
is_unlimited int,
current_log_size int,
max_log_size int,
is_log_unlimited int);

exec sp_msforeachdb ‘
insert #dbsize
select ”?”,
sum(case when filename like ”%.ldf” then 0 else size end),
sum(case when filename like ”%.ldf” then 0 else maxsize end),
min(case when filename like ”%.ldf” then 0 else maxsize end),
sum(case when filename like ”%.ldf” then size else 0 end),
sum(case when filename like ”%.ldf” then maxsize else 0 end),
min(case when filename like ”%.ldf” then maxsize else 0 end)
from [?].sys.sysfiles’;

select database_name as DatabaseName,
currrent_size / 128.0 as CurrentDBSize,
case when is_unlimited = -1 then ‘unlimited’ else str(max_size/128.0) end as MaxDBSize,
current_log_size/128.0 as CurrentLogSize,
case when is_log_unlimited = -1 then ‘unlimited’ else str(max_log_size/128.0) end as MaxLogSize
from #dbsize
order by database_name;
GO
if object_id(‘tempdb..#dbsize’) is not null
drop table #dbsize;
GO


DB SHRINK

For databases that would be in the FULL recovery model, the databases won’t be shrunk by normal process you can try the following script in MS SQL Server

Use Master
BACKUP LOG User_db TO DISK=’NUL:’ WITH NO_CHECKSUM, CONTINUE_AFTER_ERROR

Use User_db
dbcc loginfo

DBCC SHRINKFILE (user_db_log, EMPTYFILE);


All access to a specific user script in MS SQL Server

DECLARE @dbname VARCHAR(50)  
DECLARE @statement NVARCHAR(MAX)

DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM master.sys.databases
WHERE name NOT IN (‘master’,’msdb’,’model’,’tempdb’)
AND state_desc=’online’
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  

SELECT @statement = ‘use ‘+@dbname +’;’+ ‘SELECT

ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin, pp.name as PermissionLevel, pp.type_desc as TypeOfRole

FROM sys.database_role_members roles

JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id

JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
where p.name=”Test_User”’ — Change the user name

EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

(Important Scripts for MS SQL DBA)


List of jobs, selected info about jobs script in MS SQL Server

SELECT
job_id
,name
,enabled
,date_created
,date_modified
FROM msdb.dbo.sysjobs
ORDER BY date_created


To Repair DB script in MS SQL Server

ALTER DATABASE User_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DBCC CHECKDB (User_db, REPAIR_ALLOW_DATA_LOSS)
GO

ALTER DATABASE User_db SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

Read More: What is an SQL Server DBA? Are DBAs still relevant?


MS SQL Server startup parameter script

SELECT
DSR.registry_key,
DSR.value_name,
DSR.value_data
FROM sys.dm_server_registry AS DSR
WHERE
DSR.registry_key LIKE N’%MSSQLServer\Parameters’;


(Important Scripts for MS SQL DBA)

BCP Utility to copy the big tables from one database to another script in MS SQL Server

NOTE: Create the destination table before copy the bkp table

bcp User_db.dbo.Table_name out “L:\DBA_Backup\Table_name.txt” -c -T

bcp User_db.dbo.Table_name in “C:\fruit\inventory.txt” -c -T

(Important Scripts for MS SQL DBA)


DB comparison script – table and row count script in MS SQL Server

DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString + ‘ UNION ALL ‘,”)
+ ‘SELECT ‘
+ ”” + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
+ ‘.’ + QUOTENAME(sOBJ.name) + ”” + ‘ AS [TableName]
, COUNT(*) AS [RowCount] FROM ‘
+ QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
+ ‘.’ + QUOTENAME(sOBJ.name) + ‘ WITH (NOLOCK) ‘
FROM sys.objects AS sOBJ
WHERE
sOBJ.type = ‘U’
AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString
GO


HIGH CPU utilization script in MS SQL Server

SELECT TOP 10 s.session_id,
r.status,
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
r.total_elapsed_time / (1000 * 60) ‘Elaps M’,
SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.TEXT)
ELSE r.statement_end_offset
END – r.statement_start_offset) / 2) + 1) AS statement_text,
COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N’.’ + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid))
+ N’.’ + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), ”) AS command_text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC


Missing Index script in MS SQL Server

–Use the following query to identify queries with high CPU usage that contain at least one missing index in the query plan.

— Captures the Total CPU time spent by a query along with the query plan and total executions
SELECT
qs_cpu.total_worker_time/1000 AS total_cpu_time_ms,
q.[text],
p.query_plan,
qs_cpu.execution_count,
q.dbid,
q.objectid,
q.encrypted AS text_encrypted
FROM
(SELECT TOP 500 qs.plan_handle,
qs.total_worker_time,
qs.execution_count
FROM sys.dm_exec_query_stats qs
ORDER BY qs.total_worker_time DESC) AS qs_cpu
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
CROSS APPLY sys.dm_exec_query_plan (plan_handle) p
WHERE p.query_plan.exist(‘declare namespace
qplan=”http://schemas.microsoft.com/sqlserver/2004/07/showplan”;
//qplan:MissingIndexes’)=1

(Important Scripts for MS SQL DBA)


To check the Memory issue in the SQL Server

SELECT total_physical_memory_kb/1024 [Total Physical Memory in MB],
available_physical_memory_kb/1024 [Physical Memory Available in MB],
system_memory_state_desc
FROM sys.dm_os_sys_memory;

SELECT physical_memory_in_use_kb/1024 [Physical Memory Used in MB],
process_physical_memory_low [Physical Memory Low],
process_virtual_memory_low [Virtual Memory Low]
FROM sys.dm_os_process_memory;

SELECT committed_kb/1024 [SQL Server Committed Memory in MB],
committed_target_kb/1024 [SQL Server Target Committed Memory in MB]
FROM sys.dm_os_sys_info;


To check the Disk space of the server

declare @svrName varchar(255)
declare @sql varchar(400)
–by default it will take the current server name, we can the set the server name as well
set @svrName = @@SERVERNAME
set @sql = ‘powershell.exe -c “Get-WmiObject -Class Win32_Volume -Filter ”DriveType = 3” | select name,capacity,freespace | foreach{$_.name+”|”+$_.capacity/1048576+”%”+$_.freespace/1048576+”*”}”‘

Print @sql
–creating a temporary table
CREATE TABLE #output
(line varchar(255))
–inserting disk name, total space and free space value in to temporary table
insert #output
EXEC xp_cmdshell @sql

–script to retrieve the values in GB from PS Script output
select @@SERVERNAME as servername ,rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(‘|’,line) -1))) as drivename
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘|’,line)+1,
(CHARINDEX(‘%’,line) -1)-CHARINDEX(‘|’,line)) )) as Float)/1024,0) as ‘capacityGB’
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘%’,line)+1,
(CHARINDEX(‘*’,line) -1)-CHARINDEX(‘%’,line)) )) as Float) /1024 ,0)as ‘freespaceGB’,
round(100 * (round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘%’,line)+1,
(CHARINDEX(‘*’,line) -1)-CHARINDEX(‘%’,line)) )) as Float) /1024 ,0))/
(round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘|’,line)+1,
(CHARINDEX(‘%’,line) -1)-CHARINDEX(‘|’,line)) )) as Float)/1024,0)),0) as percentfree

from #output
–select * from #output
where line like ‘[A-Z][:]%’
order by drivename
–script to drop the temporary table
drop table #output
GO

–sp_configure ‘xp_cmdshell’, 0
–GO

–Reconfigure with override
–GO

–sp_configure ‘show advanced options’, 0
–GO

–Reconfigure with override
–GO

(Important Scripts for MS SQL DBA)


To check Execution plans stored in the plan cache of the MS SQL Server

SELECT cp.usecounts, cp.cacheobjtype, cp.objtype, st.text, qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY cp.usecounts DESC


To check login password expiration in MS SQL Server

SELECT SL.name AS LoginName
,LOGINPROPERTY (SL.name, ‘PasswordLastSetTime’) AS PasswordLastSetTime
,LOGINPROPERTY (SL.name, ‘DaysUntilExpiration’) AS DaysUntilExpiration
,DATEADD(dd, CONVERT(int, LOGINPROPERTY (SL.name, ‘DaysUntilExpiration’))
, CONVERT(datetime, LOGINPROPERTY (SL.name, ‘PasswordLastSetTime’))) AS PasswordExpiration
,SL.is_policy_checked AS IsPolicyChecked
,LOGINPROPERTY (SL.name, ‘IsExpired’) AS IsExpired
,LOGINPROPERTY (SL.name, ‘IsMustChange’) AS IsMustChange
,LOGINPROPERTY (SL.name, ‘IsLocked’) AS IsLocked
,LOGINPROPERTY (SL.name, ‘LockoutTime’) AS LockoutTime
,LOGINPROPERTY (SL.name, ‘BadPasswordCount’) AS BadPasswordCount
,LOGINPROPERTY (SL.name, ‘BadPasswordTime’) AS BadPasswordTime
,LOGINPROPERTY (SL.name, ‘HistoryLength’) AS HistoryLength
FROM sys.sql_logins AS SL
WHERE is_expiration_checked = 1
ORDER BY LOGINPROPERTY (SL.name, ‘PasswordLastSetTime’) DESC


Buffer Cache Hit Ratio

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Buffer Manager%’
AND [counter_name] = ‘Buffer cache hit ratio’


Page Life Expectancy

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Buffer Manager%’
AND [counter_name] = ‘Page life expectancy’

(Important Scripts for MS SQL DBA)


To check the blocking session

SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
GO

SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id <> 0
GO

Select session_id,
host_name,
program_name,
login_name,
status,
logical_reads
from sys.dm_exec_sessions
where session_id=63

select session_id,
status,
command,
sql_handle,
database_id,
blocking_session_id
from sys.dm_exec_requests
where session_id=63

select * from sys.dm_exec_sql_text
(xxxx)


Transfer logins and passwords Always-on availability group SQL Server

USE [master]
GO
IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE [dbo].[sp_hexadecimal]
(
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
)
AS
BEGIN
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = ‘0x’
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = ‘0123456789ABCDEF’

BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint – (@firstint*16)
SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue

END
go
IF OBJECT_ID (‘sp_help_revlogin’) IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE [dbo].[sp_help_revlogin]
(
@login_name sysname = NULL
)
AS
BEGIN
DECLARE @name SYSNAME
DECLARE @type VARCHAR (1)
DECLARE @hasaccess INT
DECLARE @denylogin INT
DECLARE @is_disabled INT
DECLARE @PWD_varbinary VARBINARY (256)
DECLARE @PWD_string VARCHAR (514)
DECLARE @SID_varbinary VARBINARY (85)
DECLARE @SID_string VARCHAR (514)
DECLARE @tmpstr VARCHAR (1024)
DECLARE @is_policy_checked VARCHAR (3)
DECLARE @is_expiration_checked VARCHAR (3)
Declare @Prefix VARCHAR(255)
DECLARE @defaultdb SYSNAME
DECLARE @defaultlanguage SYSNAME
DECLARE @tmpstrRole VARCHAR (1024)

IF (@login_name IS NULL)
BEGIN
DECLARE login_curs CURSOR
FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name
FROM sys.server_principals p
LEFT JOIN sys.syslogins l ON ( l.name = p.name )
WHERE p.type IN ( ‘S’, ‘G’, ‘U’ )
AND p.name <> ‘sa’
ORDER BY p.name
END
ELSE
DECLARE login_curs CURSOR
FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name
FROM sys.server_principals p
LEFT JOIN sys.syslogins l ON ( l.name = p.name )
WHERE p.type IN ( ‘S’, ‘G’, ‘U’ )
AND p.name = @login_name
ORDER BY p.name

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage
IF (@@fetch_status = -1)
BEGIN
PRINT ‘No login(s) found.’
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = ‘/* sp_help_revlogin script ‘
PRINT @tmpstr
SET @tmpstr = ‘** Generated ‘ + CONVERT (varchar, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */’
PRINT @tmpstr
PRINT ”
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ”
SET @tmpstr = ‘– Login: ‘ + @name
PRINT @tmpstr
SET @tmpstr=’IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N”’+@name+”’)
BEGIN’
Print @tmpstr
IF (@type IN ( ‘G’, ‘U’))
BEGIN — NT authenticated account/group
SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ FROM WINDOWS WITH DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’
END
ELSE
BEGIN — SQL Server authentication
— obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, ‘PasswordHash’ ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
— obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END
FROM sys.sql_logins
WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END
FROM sys.sql_logins
WHERE name = @name
SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ WITH PASSWORD = ‘ + @PWD_string + ‘ HASHED, SID = ‘
+ @SID_string + ‘, DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’ + ‘, DEFAULT_LANGUAGE = [‘ + @defaultlanguage + ‘]’
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ‘, CHECK_POLICY = ‘ + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ‘, CHECK_EXPIRATION = ‘ + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN — login is denied access
SET @tmpstr = @tmpstr + ‘; DENY CONNECT SQL TO ‘ + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN — login exists but does not have access
SET @tmpstr = @tmpstr + ‘; REVOKE CONNECT SQL TO ‘ + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN — login is disabled
SET @tmpstr = @tmpstr + ‘; ALTER LOGIN ‘ + QUOTENAME( @name ) + ‘ DISABLE’
END
SET @Prefix = ‘
EXEC master.dbo.sp_addsrvrolemember @loginame=”’
SET @tmpstrRole=”
SELECT @tmpstrRole = @tmpstrRole
+ CASE WHEN sysadmin = 1 THEN @Prefix + [LoginName] + ”’, @rolename=”sysadmin”’ ELSE ” END
+ CASE WHEN securityadmin = 1 THEN @Prefix + [LoginName] + ”’, @rolename=”securityadmin”’ ELSE ” END
+ CASE WHEN serveradmin = 1 THEN @Prefix + [LoginName] + ”’, @rolename=”serveradmin”’ ELSE ” END
+ CASE WHEN setupadmin = 1 THEN @Prefix + [LoginName] + ”’, @rolename=”setupadmin”’ ELSE ” END
+ CASE WHEN processadmin = 1 THEN @Prefix + [LoginName] + ”’, @rolename=”processadmin”’ ELSE ” END
+ CASE WHEN diskadmin = 1 THEN @Prefix + [LoginName] + ”’, @rolename=”diskadmin”’ ELSE ” END
+ CASE WHEN dbcreator = 1 THEN @Prefix + [LoginName] + ”’, @rolename=”dbcreator”’ ELSE ” END
+ CASE WHEN bulkadmin = 1 THEN @Prefix + [LoginName] + ”’, @rolename=”bulkadmin”’ ELSE ” END
FROM (
SELECT CONVERT(VARCHAR(100),SUSER_SNAME(sid)) AS [LoginName],
sysadmin,
securityadmin,
serveradmin,
setupadmin,
processadmin,
diskadmin,
dbcreator,
bulkadmin
FROM sys.syslogins
WHERE ( sysadmin<>0
OR securityadmin<>0
OR serveradmin<>0
OR setupadmin <>0
OR processadmin <>0
OR diskadmin<>0
OR dbcreator<>0
OR bulkadmin<>0
)
AND name=@name
) L
PRINT @tmpstr
PRINT @tmpstrRole
PRINT ‘END’
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
END


(Important Scripts for MS SQL DBA)

Script out all DB mail profile

USE msdb 
GO 
 
Declare @TheResults varchar(max), 
        @vbCrLf CHAR(2) 
SET @vbCrLf = CHAR(13) + CHAR(10)         
SET @TheResults = ' 
use master 
go 
sp_configure ''show advanced options'',1 
go 
reconfigure with override 
go 
sp_configure ''Database Mail XPs'',1 
--go 
--sp_configure ''SQL Mail XPs'',0 
go 
reconfigure 
go 
' 
SELECT @TheResults = @TheResults  + ' 
-------------------------------------------------------------------------------------------------- 
-- BEGIN Mail Settings ' + p.name + ' 
-------------------------------------------------------------------------------------------------- 
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = ''' + p.name + ''')  
  BEGIN 
    --CREATE Profile [' + p.name + '] 
    EXECUTE msdb.dbo.sysmail_add_profile_sp 
      @profile_name = ''' + p.name + ''', 
      @description  = ''' + ISNULL(p.description,'') + '''; 
  END --IF EXISTS profile 
  ' 
  + 
  ' 
  IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = ''' + a.name + ''') 
  BEGIN 
    --CREATE Account [' + a.name + '] 
    EXECUTE msdb.dbo.sysmail_add_account_sp 
    @account_name            = ' + CASE WHEN a.name                IS NULL THEN ' NULL ' ELSE + '''' + a.name                  + '''' END + ', 
    @email_address           = ' + CASE WHEN a.email_address       IS NULL THEN ' NULL ' ELSE + '''' + a.email_address         + '''' END + ', 
    @display_name            = ' + CASE WHEN a.display_name        IS NULL THEN ' NULL ' ELSE + '''' + a.display_name          + '''' END + ', 
    @replyto_address         = ' + CASE WHEN a.replyto_address     IS NULL THEN ' NULL ' ELSE + '''' + a.replyto_address       + '''' END + ', 
    @description             = ' + CASE WHEN a.description         IS NULL THEN ' NULL ' ELSE + '''' + a.description           + '''' END + ', 
    @mailserver_name         = ' + CASE WHEN s.servername          IS NULL THEN ' NULL ' ELSE + '''' + s.servername            + '''' END + ', 
    @mailserver_type         = ' + CASE WHEN s.servertype          IS NULL THEN ' NULL ' ELSE + '''' + s.servertype            + '''' END + ', 
    @port                    = ' + CASE WHEN s.port                IS NULL THEN ' NULL ' ELSE + '''' + CONVERT(VARCHAR,s.port) + '''' END + ', 
    @username                = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''' + c.credential_identity   + '''' END + ', 
    @password                = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''NotTheRealPassword''' END + ',  
    @use_default_credentials = ' + CASE WHEN s.use_default_credentials = 1 THEN ' 1 ' ELSE ' 0 ' END + ', 
    @enable_ssl              = ' + CASE WHEN s.enable_ssl = 1              THEN ' 1 ' ELSE ' 0 ' END + '; 
  END --IF EXISTS  account 
  ' 
  + ' 
IF NOT EXISTS(SELECT * 
              FROM msdb.dbo.sysmail_profileaccount pa 
                INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id 
                INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id   
              WHERE p.name = ''' + p.name + ''' 
                AND a.name = ''' + a.name + ''')  
  BEGIN 
    -- Associate Account [' + a.name + '] to Profile [' + p.name + '] 
    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp 
      @profile_name = ''' + p.name + ''', 
      @account_name = ''' + a.name + ''', 
      @sequence_number = ' + CONVERT(VARCHAR,pa.sequence_number) + ' ; 
  END  
--IF EXISTS associate accounts to profiles 
--------------------------------------------------------------------------------------------------- 
-- Drop Settings For ' + p.name + ' 
-------------------------------------------------------------------------------------------------- 
/* 
IF EXISTS(SELECT * 
            FROM msdb.dbo.sysmail_profileaccount pa 
              INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id 
              INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id   
            WHERE p.name = ''' + p.name + ''' 
              AND a.name = ''' + a.name + ''') 
  BEGIN 
    EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = ''' + p.name + ''',@account_name = ''' + a.name + ''' 
  END  
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = ''' + a.name + ''') 
  BEGIN 
    EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = ''' + a.name + ''' 
  END 
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = ''' + p.name + ''')  
  BEGIN 
    EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = ''' + p.name + ''' 
  END 
*/ 
  '  
FROM msdb.dbo.sysmail_profile p 
INNER JOIN msdb.dbo.sysmail_profileaccount pa ON  p.profile_id = pa.profile_id 
INNER JOIN msdb.dbo.sysmail_account a         ON pa.account_id = a.account_id  
LEFT OUTER JOIN msdb.dbo.sysmail_server s     ON a.account_id = s.account_id 
LEFT OUTER JOIN sys.credentials c    ON s.credential_id = c.credential_id 
 
   ;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL   
                    SELECT 1 UNION ALL SELECT 1 UNION ALL   
                    SELECT 1 UNION ALL SELECT 1 UNION ALL   
                    SELECT 1 UNION ALL SELECT 1 UNION ALL   
                    SELECT 1 UNION ALL SELECT 1), --         10 or 10E01 rows   
         E02(N) AS (SELECT 1 FROM E01 a, E01 b),  --        100 or 10E02 rows   
         E04(N) AS (SELECT 1 FROM E02 a, E02 b),  --     10,000 or 10E04 rows   
         E08(N) AS (SELECT 1 FROM E04 a, E04 b),  --100,000,000 or 10E08 rows   
         --E16(N) AS (SELECT 1 FROM E08 a, E08 b),  --10E16 or more rows than you'll EVER need,   
         Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),   
       ItemSplit(   
                 ItemOrder,   
                 Item   
                ) as (   
                      SELECT N,   
                        SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf))   
                      FROM Tally   
                      WHERE N < DATALENGTH(@vbCrLf + @TheResults)   
                      --WHERE N < DATALENGTH(@vbCrLf + @INPUT) -- REMOVED added @vbCrLf   
                        AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter   
                     )   
  select   
    row_number() over (order by ItemOrder) as ItemID,   
    Item   
  from ItemSplit

==================================================================

(Important Scripts for MS SQL DBA)

Script to PBI reports

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top