How to know who dropped a table in SQL Server by T-SQL

There are a lot of questions about this topic , who has deleted the object from Production environment. How to identify it in SQL Server? Can't i trace it, the answer it yes you can. There is one undocumented function fn_dblog() which can be used for this purpose.

fn_dblog() allows to view the transaction log as a part of transaction log file. This function accepts two parameters

The first is starting log sequence number, or LSN or NULL.

The second is the ending LSN or  NULL.

When we run query given below

SELECT * FROM fn_dblog(NULL, NULL) 

it gives us all of the transaction log file records as below

If we need to filter transactions based on insert, update & delete command then we can use query like below

SQL

select * 
 
FROM   fn_dblog(null,null) 
 
where Operation IN  
 
   ('LOP_INSERT_ROWS','LOP_MODIFY_ROW', 
 
    'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT') 

Now how to identify to know the details of deleted objects 

I have created a table TestDrop to test. I collected the information from sys.objects about the table which gives me object_id=295672101 for table TestDrop. In next SQL Statement i dropped the table.

SQL

CREATE TABLE [TestDrop] ( 
 
Id INT IDENTITY, 
 
Name VARCHAR (100)); 
 
GO 
 
INSERT INTO [TestDrop] values('Test'); 
 
GO 
 
Select  object_id, name  from sys.objects where type='U' 
 
GO 
 
DROP TABLE [TestDrop]; 
 
GO

After dropping the table we will not get any information from sys.objects so we have to make sure that we are taking backup of this table to have that information somewhere otherwise we have to lookup the dropped object information with the databases previously taken  backups. Now we will try to get the object id for the table which is dropped.

SQL

SELECT [Transaction Id], [Begin Time], SUSER_SNAME ([Transaction SID]) AS [User] 
 
FROM fn_dblog (NULL, NULL) 
 
WHERE [Transaction Name] = N'DROPOBJ';

Above query will give us the Transaction id and tell us that the table is dropped and who has dropped it but it does not tell us which object has been dropped, to identify this we will use Transaction id , in my case it gives me Transaction id  "0000:00002a3a" which can be further used in the following query to get the object id

SQL

SELECT TOP (1) [Lock Information] FROM fn_dblog (NULL, NULL) 
 
WHERE [Transaction Id] = '0000:00002a3a' 
 
AND [Lock Information] LIKE '%SCH_M OBJECT%';

this query gives us the "HoBt 0:ACQUIRE_LOCK_SCH_M OBJECT: 1:295672101:0 " and in this string the 1:295672101:0 contains the database id and object id. Before first occurrence of ";" is the database id and the next one is object id.

We can use 2956721010 with previous database backup to know which object is dropped.

I have created  complete script "Get_Info_about_dropped_Object.sql" which you can download form the link below, where you need not to pass object_id manually, the only requirement is you should have a periodic backup of sys.objects table. In my case i have done it temporarly in a table "#databaseTables".

Download Link: https://gallery.technet.microsoft.com/How-to-know-who-dropped-a-b5c2a244

cheers,