SQL SERVER – Reading Transaction Log to Identified Who Dropped a Table

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)