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
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.
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.
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
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