This article is half-done without your Comment! *** Please share your thoughts via Comment ***
SQL Server provides different types of database auditing option like: CDC, Database Audit.
Recently, I have shared one article about, change your database DDL changes using Encrypted DDL triggers.
SQL Server: Encrypted DDL Trigger to Track all Database Changes
Database Administrators are responsible for database security and Periodically, They have to track all the database related changes and make sure that everything is working fine.
But still sometimes, I used to track database changes from the SQL Server log files using fn_dblog().
I am sharing one way to find a person who dropped a table and also can get the name of that dropped table without enabling any auditing option of SQL Server.
Script to find the log of dropped objects:
Using this script, you can find a list of dropped tables and login name information, but you cannot get table name directly.
You can find the transaction id for each record and using this transaction ID, you can find the table name.
|
1 2 3 4 5 6 |
SELECT [Transaction Id] ,[Begin Time] ,SUSER_SNAME ([Transaction SID]) AS [LoginName] FROM fn_dblog (NULL, NULL) WHERE [Transaction Name] = N'DROPOBJ'; |
Find Table name using TransactionID:
|
1 2 3 4 5 |
SELECT TOP 1 [Lock Information] FROM fn_dblog (NULL, NULL) WHERE [Transaction Id] = '0000:0015d6b3' AND [Lock Information] LIKE '%SCH_M OBJECT%'; |
The result:
To get the table name, you can copy the number and search into sys.objects table.
|
1 |
HoBt 0:ACQUIRE_LOCK_SCH_M OBJECT: 8:1015674666:0 |
Find your dropped table:
Please do not start to search your dropped table in your current database because Table already dropped.
You must restore the last backup of the database and you should search your dropped table in that database.
|
1 2 |
SELECT *FROM sys.objects WHERE Object_ID ='1015674666' |
