The other day I received a distress email from a user who wanted to find out who has dropped a table or column from their database. During the On Demand (55 minutes) consultation, not only I was able to help them with restoring their lost data but also was able to find out which user had actually dropped their table “accidentally”. We were lucky enough that the user had a database in full recovery model, hence their transaction log recorded every detail needed.
Let us see the quick demonstration for the same.
First, let us create a table in a database.
CREATE DATABASE WhoDroppedTable GO USE WhoDroppedTable GO CREATE TABLE DroppedTable (ID INT, Col1 VARCHAR(100)) GO
Next, drop the table inside the database.
-- Login with user [CarelessLogin] USE [WhoDroppedTable] GO DROP TABLE dbo.DroppedTable GO
Next, run the fn_dblog command to search for DROPOBJ and you will find the record with the username associated with it.
SELECT [Transaction Id], [Begin Time], SUSER_SNAME ([Transaction SID]) AS [User], [Transaction Name] FROM fn_dblog (NULL, NULL) WHERE [Transaction Name] = 'DROPOBJ' GO
Yes, it is that simple to identify the user who runs the drop command. Now you can run the following clean up job.
-- Clean up USE master GO DROP DATABASE WhoDroppedTable GO
Please remember, if you are using SA or system admin login for accessing your system, you will be not able to find an ultimate user who dropped your table. It is always a good idea to create a different login and user for your system so when you have to track down any user it is easier for you.
Reference: Pinal Dave (https://blog.sqlauthority.com)