how to check who drop table from database

how to check who deleted table from database

I have heard many times that the data has been deleted but no one takes responsibility that who has deleted so how can we find out who has deleted the data.

Even I have received so many emails in the past from developers/ DBA and they wanted to know who has dropped a table or column from their database.

You can use this script to get a list of dropped tables and information about login names, but you cannot directly get table names.

For every record, you can get the transaction id, and with the help of this transaction id, you can get the table name.

 

SELECT

            [Transaction Id]

            ,[Begin Time]

            ,SUSER_SNAME ([Transaction SID]) AS [LoginName]

FROM fn_dblog (NULL, NULL) 

WHERE [Transaction Name] = N'DROPOBJ';

 

SELECT TOP 1

            [Lock Information]

FROM fn_dblog (NULL, NULL) 

WHERE [Transaction Id] = '0000:00006e15' 

AND [Lock Information] LIKE '%SCH_M OBJECT%';

 

Result

HoBt 0:ACQUIRE_LOCK_SCH_M OBJECT: 1:1104722988:0

 

SELECT *FROM sys.objects

WHERE Object_ID ='1104722988'