How to Track User That Dropped/Deleted TSQL on SQL Server Objects

This is the most important part for DBAs or developers. Yesterday somebody dropped or deleted objects from my database, but no one will say who. And I want to track who did it. By using the transaction log we can track which user dropped or deleted my database objects.

I have already discussed how to track the transaction log in my previous article How to track database transaction Log in SQL Server 2012. Before reading this article, I recommend you read the previous article to understand the transaction log for all database activity.

Let's create a small Test Environment using the Transaction Log and the undocumented function "fn_dblog”.

The following is the test environment.

-Tracking a user who a Deleted TSQL

Step 1: Now I will create a database and a table:

--Create Test DB.
USE [master];
GO
CREATE DATABASE TrackingDBLog;
GO
-- Create tables.
USE TrackingDBLog;
GO
CREATE TABLE [Test] (
[ID] INT IDENTITY (1, 1),
[Date] DATETIME DEFAULT GETDATE (),
[Name] CHAR (25) DEFAULT 'A');

Step 2: Now insert some data into the Test table:

USE [TrackingDBLog];
GO
SET NOCOUNT ON;
GO
INSERT INTO [Test] DEFAULT VALUES ;
GO 50
GO
SELECT COUNT(*) TotalCount FROM [Test]
GO

 

Step 3: Now delete some data from the Test table

USE TrackingDBLog
GO
DELETE FROM [Test]
GO
SELECT * FROM [Test]
GO

Step 4: Now I will find the deleted rows information using the Transaction Log and the function "fn_dblog”. Run the following command to get the info about all the deleted transactions.

USE TrackingDBLog
GO
SELECT DISTINCT
[Transaction ID],
Operation,
Context,
AllocUnitName

FROM
fn_dblog(NULL, NULL)
WHERE
Operation = 'LOP_DELETE_ROWS'

 

In the above screen we can check that the last row says a Delete statement has been performed on a HEAP table "Test" in the "AllocUnitName" column under transaction ID- 0000:00000513.

Step 5: Now I will get the User Name that deleted the data from the "Test" table by using the Transaction SID and the preceding Transaction ID- 0000:00000513. Run the following command to get the information about the Transaction SID.

USE TrackingDBLog
GO
SELECT
Operation,
[Transaction ID],
[Begin Time],
[Transaction Name],
[Transaction SID],
SUSER_SNAME ([Transaction SID]) username
FROM fn_dblog (NULL, NULL)
WHERE [Transaction ID] ='0000:00000513'
AND [Operation] ='LOP_BEGIN_XACT'
GO

Now I can see the [Begin Time] of this transaction that will also help filter out the possibilities in determining the exact info like when the data was deleted by the user.

Determining  the user who droppped in TSQL

Step 1: Now to drop a table, the Test table

USE TrackingDBLog
GO
DROP TABLE [Test]

Step 2: Now to determine the dropped table information using the Transaction Log and the function "fn_dblog”. Run the following command to get the info about all the dropped transactions.

USE TrackingDBLog
GO
SELECT
Operation,
[Transaction ID],
[Begin Time],
[Transaction Name],
[Transaction SID],
SUSER_SNAME ([Transaction SID]) username
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'

Now I can see the [Begin Time] of this transaction that will also help filter out the possibilities in determining the exact info, like when the table was dropped by the user.