Abhilash A: scripts

Who issued " DROP Database " command ?

Sometimes it is hard to track who dropped a SQL Server database, especially when you are in a large shop managing several databases. It should have been dropped by one of your team members as part of any decommission request or the application users themselves; who have enough privileges on the DB or Server. Here is one quick script I use. If we are lucky enough, we will find an entry in the default trace.😎

DECLARE @FileName VARCHAR(255)

SELECT @FileName = SUBSTRING(path, 0, LEN(path) - CHARINDEX('\', REVERSE(path)) + 1) + '\Log.trc' --'
FROM sys.traces
WHERE is_default = 1;

SELECT TOP 1 LoginName
	,HostName
	,ApplicationName
	,StartTime
	,TextData
FROM sys.fn_trace_gettable(@FileName, DEFAULT) AS gt
WHERE EventClass = 47
	AND DatabaseName = 'Pass DB Name here'
ORDER BY StartTime DESC;

Hope it helps !!

SQL Server Blocking Email Notification

Blocking is common in any RDBMS product, but it is important to get a notification if it is unusual. Steps to set up an automated email notification for any blocking greater than 30 min are:-

Step 1: Configure Database Mail

You can go here to setup DatabaseMail if you don't have one already

Step 2: Create Stored Procedure in the DBA utility database or any database using the below script. The script makes use of the SQL Server DatabaseMail & SQL Server Agent feature to send Blocking Notification either to the Database Team or Application Team if it is greater than 30 minutes. You can adjust the duration based on your business requirement.

USE DBA_DB --Change Database Name
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[BlockingReport]
AS
IF object_id('tempdb..#temp_requests') IS NOT NULL
	DROP TABLE #Password

SELECT @@servername AS [ServerName]
	,[Blocker SPID] = B.[SPID]
	,[Blocker Last Batch] = B.[Last_Batch]
	,[Blocker Open Tran] = B.[Open_Tran]
	,
	--[Blocked For] = convert(varchar,(V.waittime/(1000))/(60*60)) + ' Hours' + convert(varchar,(V.waittime/(1000))/60) + ' Minutes ' + convert(varchar,(V.waittime/(1000))%60) + ' Seconds' ,
	[Blocked For_hh:mm:ss] = convert(VARCHAR, dateadd(ms, V.waittime, 0), 114)
	,[Blocker Login Name] = B.[LogiName]
	,[Blocker Host Name] = B.[HostName]
	,[Blocker Program Name] = B.[Program_Name]
	,[Blocker status] = B.[Status]
	,[Blocker Db Name] = DB_NAME(B.[DBID])
	,[Blocker Command Text] = BlockerText.[text]
	,[Victim SPID] = V.[SPID]
	,[Victim Last Batch] = V.[Last_Batch]
	,[Victim Open Tran] = V.[Open_Tran]
	,[Victim Wait Time_hh:mm:ss] = convert(VARCHAR, dateadd(ms, V.waittime, 0), 114)
	,[Victim Login Name] = V.[LogiName]
	,[Victim Host Name] = V.[HostName]
	,[Victim Program Name] = V.[Program_Name]
	,[Victim status] = V.[Status]
	,[Victim Db Name] = DB_NAME(V.[DBID])
	,[Victim Command Text] = VictimText.[text]
INTO #temp_requests
FROM [master].[dbo].[sysprocesses] B(NOLOCK)
JOIN [master].[dbo].[sysprocesses] V(NOLOCK) ON V.Blocked = B.SPID
OUTER APPLY [sys].[dm_exec_sql_text](B.sql_handle) BlockerText
OUTER APPLY [sys].[dm_exec_sql_text](V.sql_handle) VictimText
WHERE V.waittime > 1000 * 60 * 30 --30 Min
	AND V.Blocked != 0
	AND V.Blocked != V.SPID

DECLARE @count INT

SET @Count = @@ROWCOUNT

IF @Count > 0
BEGIN
	BEGIN
		-- Email Querry--
		DECLARE @Body VARCHAR(max)
		DECLARE @TableHead VARCHAR(max)
		DECLARE @TableTail VARCHAR(max)
		DECLARE @mailitem_id AS INT
		DECLARE @statusMsg AS VARCHAR(max)
		DECLARE @Error AS VARCHAR(max)
		DECLARE @Note AS VARCHAR(max)

		SET NOCOUNT ON;
		SET @mailitem_id = NULL
		SET @statusMsg = NULL
		SET @Error = NULL
		SET @Note = NULL
		SET @TableTail = '</table>
<br/>Please review the Blocking Queries & take necessary action<br/>
<br/>Thanks<br/>DBA Team</body></html>';
		--HTML layout--
		SET @TableHead = '<html><head>' + 'Hello,<br/>
<br/>

<br/>
Please find the blocking details on SQL server  :  <br/>
<br/>
' + '<style>' + 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;color:Black;} ' + '</style>' + '</head>' + '<body><table cellpadding=0 cellspacing=0 border=0>' + '<tr bgcolor=#F6AC5D>' + '<td align=center><b>Server Name </b></td>' + '<td align=center><b>Blocker SPID </b></td>' + '<td align=center><b>Blocker Last Batch</b></td>' + '<td align=center><b> Blocker Open Tran </b></td>' + '<td align=center><b>Blocked For_hh:mm:ss </b></td>' + '<td align=center><b>Blocker Login Name </b></td>' + '<td align=center><b> Blocker Host Name</b></td>' + '<td align=center><b>Blocker Program Name </b></td>' + '<td align=center><b>Blocker status</b></td>' + '<td align=center><b>Blocker Db Name</b></td>' + '<td align=center><b> Blocker Command Text</b></td>' + '<td align=center><b> Victim SPID </b></td>' + 
			'<td align=center><b>Victim Last Batch</b></td>' + '<td align=center><b> Victim Open Tran </b></td>' + '<td align=center><b> Victim Wait Time_hh:mm:ss </b></td>' + '<td align=center><b> Victim Login Name </b></td>' + '<td align=center><b> Victim Host Name</b></td>' + '<td align=center><b> Victim Program Name </b></td>' + '<td align=center><b> Victim status</b></td>' + '<td align=center><b> Victim Db Name</b></td>' + '<td align=center><b> Victim Command Text</b></td></tr>';

		SELECT @Body = (
				SELECT [ServerName] AS [TD]
					,[Blocker SPID] AS [TD]
					,[Blocker Last Batch] AS [TD]
					,[Blocker Open Tran] AS [TD]
					,[Blocked For_hh:mm:ss] AS [TD]
					,[Blocker Login Name] AS [TD]
					,[Blocker Host Name] AS [TD]
					,[Blocker Program Name] AS [TD]
					,[Blocker status] AS [TD]
					,[Blocker Db Name] AS [TD]
					,[Blocker Command Text] AS [TD]
					,[Victim SPID] AS [TD]
					,[Victim Last Batch] AS [TD]
					,[Victim Open Tran] AS [TD]
					,[Victim Wait Time_hh:mm:ss] AS [TD]
					,[Victim Login Name] AS [TD]
					,[Victim Host Name] AS [TD]
					,[Victim Program Name] AS [TD]
					,[Victim status] AS [TD]
					,[Victim Db Name] AS [TD]
					,[Victim Command Text] AS [TD]
				FROM #temp_requests
				FOR XML raw('tr')
					,Elements
				)

		SET @Body = Replace(@Body, '_x0020_', space(1))
		SET @Body = Replace(@Body, '_x003D_', '=')
		SET @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
		SET @Body = Replace(@Body, '<TRRow>0</TRRow>', '')
		SET @Body = @TableHead + @Body + @TableTail

		EXEC msdb.dbo.sp_send_dbmail @profile_name = ''
			,-- replace with your SQL Database Mail Profile 
			@body = @body
			,@body_format = 'HTML'
			,@recipients = ''
			,-- replace with your email address
			@subject = 'Blocking Detected';

		--EXEC msdb.dbo.sysmail_help_profile_sp;
		DROP TABLE #temp_requests
	END
END

Step 3: Schedule a SQL Server Agent job to execute the Stored Procedure created(in step 2) to run every 15 minutes

Done!! I hope this helps 😊

Password Expiry Notification for SQL Logins

Most of the Organisation enforces password expiration policies to manage the lifespan of a password. When SQL Server enforces password expiration policy, users must reset the password. Steps to set up an automated email notification 7 days prior to the actual date of expiry are:-

Step 1: Configure Database Mail

You can go here to setup DatabaseMail if you don't have one already

Step 2: Create Stored Procedure in the DBA utility database or any database using the below script. The script makes use of the SQL Server DatabaseMail & SQL Server Agent feature to send Password Expiration Notification either to the Database Team or Application Team 1 week before the expiry date.

USE DBA_DB --Change Database Name
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[PasswordExpiration_SP]
AS
IF object_id('tempdb..#Password') IS NOT NULL
	DROP TABLE #Password

DECLARE @count INT

SELECT @@Servername AS ServerName
	,'ITG' AS Env
	,SL.name AS LoginName
	,LOGINPROPERTY(SL.name, 'DaysUntilExpiration') AS DaysUntilExpiration
	,LOGINPROPERTY(SL.name, 'IsExpired') AS IsExpired
INTO #Password
FROM sys.sql_logins AS SL
WHERE is_expiration_checked = 1
	AND is_disabled = 0
	AND sl.name NOT LIKE '##%'
	AND DATEDIFF(dd, getdate(), DATEADD(DAY, CAST(LOGINPROPERTY(SL.name, 'DaysUntilExpiration') AS INT), GETDATE())) < 7 --Change the number of days here

SET @Count = @@ROWCOUNT

--select * from #Password 
IF @Count > 0
BEGIN
	BEGIN
		-- Email Querry--
		DECLARE @Body VARCHAR(max)
		DECLARE @TableHead VARCHAR(max)
		DECLARE @TableTail VARCHAR(max)
		DECLARE @mailitem_id AS INT
		DECLARE @statusMsg AS VARCHAR(max)
		DECLARE @Error AS VARCHAR(max)
		DECLARE @Note AS VARCHAR(max)

		SET NOCOUNT ON;
		SET @mailitem_id = NULL
		SET @statusMsg = NULL
		SET @Error = NULL
		SET @Note = NULL
		SET @TableTail = '</table>
<br/>Request you to reset the password before it expires<br/>
<br/>Thanks<br/>DB Team</body></html>';
		--HTML layout--
		SET @TableHead = '<html><head>' + 'Hello,<br/>
<br/>
Greetings from DBA Team. <br/>
<br/>
Please reset password of below account(s). <br/>
<br/>
' + '<style>' + 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;color:Black;} ' + '</style>' + '</head>' + '<body><table cellpadding=0 cellspacing=0 border=0>' + '<tr bgcolor=#F6AC5D>' + '<td align=center><b>ServerName</b></td>' + '<td align=center><b>Env</b></td>' + '<td align=center><b>LoginName</b></td>' + '<td align=center><b>DaysUntilExpiration</b></td>' + '<td align=center><b>IsExpired</b></td></tr>';

		--Select information for the Report-- 
		SELECT @Body = (
				SELECT [ServerName] AS [TD]
					,[Env] AS [TD]
					,[LoginName] AS [TD]
					,[DaysUntilExpiration] AS [TD]
					,[IsExpired] AS [TD]
				FROM #Password
				FOR XML raw('tr')
					,Elements
				)

		--convert(varchar(19), [PasswordLastSetTime] + ' ' + @modtime, 20)
		-- Replace the entity codes and row numbers
		SET @Body = Replace(@Body, '_x0020_', space(1))
		SET @Body = Replace(@Body, '_x003D_', '=')
		SET @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
		SET @Body = Replace(@Body, '<TRRow>0</TRRow>', '')
		SET @Body = @TableHead + @Body + @TableTail

		-- return output--
		--Email
		EXEC msdb.dbo.sp_send_dbmail @recipients = 'mail_ID1;mail_ID2'
			,--Specify the mail ID's for which notification is required
			@profile_name = ''
			,-- Specify the Profile name
			@subject = 'Please Reset the password '
			,@body = @Body
			,@body_format = 'HTML';

		DROP TABLE #Password
	END
END

Step 3: Schedule a SQL Server Agent job to execute the Stored Procedure created(in step 2) to run daily

Done!! I hope this helps 😊

Abhilash A

Tuesday, February 2, 2021

Who issued " DROP Database " command ?

SQL Server Blocking Email Notification

Password Expiry Notification for SQL Logins

Extract DB Permission

AWS Solutions Architect

Azure Administrator Associate

Azure Database Administrator

Report Abuse

462: Microsoft SQL Server 2012/2014 Databases

Azure Fundamentals