Find who dropped Database at what time

As a DBA often we face a scenario that someone has dropped the database and we need to find out who dropped it. We can easily find the information from Schema Changes History and SQL Server Log. But here we find an excellent stored procedure to find the same at https://raresql.com.

Script

USE Master
GO

CREATE PROCEDURE Recover_Dropped_Database_Detail_Proc @Date_From DATETIME = '1900/01/01'
	,@Date_To DATETIME = '9999/12/31'
AS
	;

WITH CTE
AS (
	SELECT REPLACE(SUBSTRING(A.[RowLog Contents 0], 9, LEN(A.[RowLog Contents 0])), 0x00, 0x) AS [Database Name]
		,[Transaction ID]
	FROM fn_dblog(NULL, NULL) A
	WHERE A.[AllocUnitName] = 'sys.sysdbreg.nc1'
		AND A.[Transaction ID] IN (
			SELECT DISTINCT [TRANSACTION ID]
			FROM sys.fn_dblog(NULL, NULL)
			WHERE Context IN ('LCX_NULL')
				AND Operation IN ('LOP_BEGIN_XACT')
				AND [Transaction Name] LIKE '%dbdestroy%'
				AND CONVERT(NVARCHAR(11), [Begin Time]) BETWEEN @Date_From
					AND @Date_To
			)
	)
SELECT A.[Database Name]
	,B.[Begin Time] AS [Dropped Date & Time]
	,C.[name] AS [Dropped By User Name]
FROM CTE A
INNER JOIN fn_dblog(NULL, NULL) B ON A.[Transaction ID] = B.[Transaction ID]
	AND Context IN ('LCX_NULL')
	AND Operation IN ('LOP_BEGIN_XACT')
	AND [Transaction Name] LIKE '%dbdestroy%'
INNER JOIN sys.sysusers C ON B.[Transaction SID] = C.[Sid]
GO

-- Run the below-stored procedure to find the deleted database info
EXEC Recover_Dropped_Database_Detail_Proc
GO

Report