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:

  1. --Create Test DB.
  2. USE [master];
  3. GO
  4. CREATE DATABASE TrackingDBLog;
  5. GO
  6. -- Create tables.
  7. USE TrackingDBLog;
  8. GO
  9. CREATE TABLE [Test] (
  10. [ID] INT IDENTITY (1, 1),
  11. [Date] DATETIME DEFAULT GETDATE (),
  12. [Name] CHAR (25) DEFAULT 'A');

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

  1. USE [TrackingDBLog];
  2. GO
  3. SET NOCOUNT ON;
  4. GO
  5. INSERT INTO [Test] DEFAULT VALUES ;
  6. GO 50
  7. GO
  8. SELECT COUNT(*) TotalCount FROM [Test]
  9. GO

insert some data

Step 3: Now delete some data from the Test table
  1. USE TrackingDBLog
  2. GO
  3. DELETE FROM [Test]
  4. GO
  5. SELECT * FROM [Test]
  6. 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.

  1. USE TrackingDBLog
  2. GO
  3. SELECT DISTINCT
  4. [Transaction ID],
  5. Operation,
  6. Context,
  7. AllocUnitName
  9. FROM
  10. fn_dblog(NULL, NULL)
  11. WHERE
  12. 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.
  1. USE TrackingDBLog
  2. GO
  3. SELECT
  4. Operation,
  5. [Transaction ID],
  6. [Begin Time],
  7. [Transaction Name],
  8. [Transaction SID],
  9. SUSER_SNAME ([Transaction SID]) username
  10. FROM fn_dblog (NULL, NULL)
  11. WHERE [Transaction ID] ='0000:00000513'
  12. AND [Operation] ='LOP_BEGIN_XACT'
  13. 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
  1. USE TrackingDBLog
  2. GO
  3. 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.
  1. USE TrackingDBLog
  2. GO
  3. SELECT
  4. Operation,
  5. [Transaction ID],
  6. [Begin Time],
  7. [Transaction Name],
  8. [Transaction SID],
  9. SUSER_SNAME ([Transaction SID]) username
  10. FROM fn_dblog (NULL, NULL)
  11. 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.