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

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.

9 Comments

Join the conversation! Your thoughts help the community grow.

Hemant Srivastava12y ago
good information!
- Shivom Agarwal12y ago
  thanks hemant
Shivom Agarwal12y ago
thanks anupam
Anupam Singh12y ago
nice....
Shivom Agarwal12y ago
thanks to all
Shivom Agarwal12y ago
this is db user name
- Khan Abrar Ahmed12y ago
  Thanks sir
Khan Abrar Ahmed12y ago
Nice once sir, just wanted to confirm the name is user name of db user name.
Manju lata Yadav12y ago
nice article.