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.