Find who dropped Database at what time in SQL Server

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

Use Master

CREATE PROCEDURE Recover_Dropped_Database_Detail_Proc

@Date_From DATETIME='1900/01/01',

@Date_To DATETIME ='9999/12/31'

;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]

-- Run the below-stored procedure to find the deleted database info

EXEC Recover_Dropped_Database_Detail_Proc

Like!! I blog quite often and I genuinely thank you for your information. The article has truly peaked my interest.

Scenario

Script

Report

Provide a SQL Login ‘profiler access’ by ‘ALTER TRACE permission’

How to find which user/login disabled a job in sql server?

Comments

Categories

Recent Posts

Recent Comments

Archives

Related Posts