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.

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

insert some data

Step 3: Now delete some data from the Test table

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

delete data
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’

deleted transactions

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

Transaction

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]

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’

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

SQL Server DBA Training from Beginner to Advanced

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

Login with your site account

Register a new account

Modal title