Unveil hidden performance bottlenecks! Script exposes top resource-intensive queries & fragmentation levels. Ideal for DB health checks.
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)