Read the SQL Server Database Transaction Log

Who deleted that data?! Or worse -- who dropped that table?!  It really frustrates me when something like this occurs on the servers that I am responsible for... yet I am unaware.  Here is a quick piece to show you that yes, we can read the database transaction log, and we can figure out who made that unauthorized change.

 

First we need to create a test table.

   -- create test table
   CREATE TABLE [Personnel] (
     [UserID] INT IDENTITY(1,1),
     [UserName] VARCHAR(55),
     [Dept] CHAR (25)
   );

Ok. Now let us look in the log file to see that new table.

   -- look in log for that table creation
   SELECT
     [Current LSN],
     [Operation],
     [Transaction Name],
     [SPID],
     [Begin Time]
   FROM  
     fn_dblog(NULL,NULL)
   WHERE 
     [Transaction Name] = 'CREATE TABLE'

Current LSN	Operation	Transaction Name	SPID	BeginTime
00000020:00000f70:002c	LOP_BEGIN_XACT	CREATE TABLE	64	2014/02/05 12:10:41:093

Sweet!  Now let us write something into the table.

   -- write into test table
   INSERT dbo.Personnel (UserName,Dept)
   SELECT 'John Doe','Accounting'
   UNION
   SELECT 'Jane Doe','Mangement'

Look at our data:

   SELECT * FROM dbo.Personnel

Ok. Now we are going to do that unauthorized DELETE.

   /* delete something */
   DELETE dbo.Personnel
   WHERE UserName = 'Jane Doe'

Check the log... it is there.

   -- read the log
   SELECT
      [Transaction ID],
      [Operation],
      [Context],
      [AllocUnitName]
   FROM  
      fn_dblog(NULL,NULL)
   WHERE  
      Operation = 'LOP_DELETE_ROWS'
      AND AllocUnitName = 'dbo.Personnel'

Transaction ID	Operation	Context	AllocUnitName
0000:00000557	LOP_DELETE_ROWS	LCX_HEAP	dbo.Personnel

Now, we need to use that Transaction ID returned above to get the Transaction SID.
This will help us find the actual user who performed this action.

   -- get the transaction sid
   SELECT
      [Transaction ID],
      [Transaction SID],  
      [Operation],
      [AllocUnitName]
   FROM  
      fn_dblog(NULL,NULL)
   WHERE   
      [Transaction ID] = '0000:00000550'

 

Transaction ID	Transaction SID	Operation	AllocUnitName
0000:00000557	0x010500000000000515000000EFEE1C9FBC493493D723D1D8E8030000	LOP_DELETE_ROWS	dbo.Personnel

Ok. This is the gold! Now we use the Transaction SID to figure out who actually performed the delete!

   -- find the offending user
   USE master;
   SELECT SUSER_SNAME(0x010500000000000515000000EFEE1C9FBC493493D723D1D8E8030000) [Login]

Login

MyMachine\MyName

 
Alright. One better. Let us drop that Personnel table.

   -- drop table
   DROP TABLE dbo.Personnel

Just like before, let us get our Transaction ID, and then our Transaction SID.

   -- get transaction id
   SELECT
      [Transaction ID],
      [Transaction SID],
      [Transaction Name],
      [Operation],
      [Description]
   FROM  
      fn_dblog(NULL,NULL)
   WHERE
      [Transaction Name] = 'DROPOBJ'

And now... we find the guy who dropped the table.


   -- pass transaction sid to get user name
   USE master;
   SELECT SUSER_SNAME(0x010500000000000515000000EFEE1C9FBC493493D723D1D8E8030000) [Login]


It is undocumented, so you need to be cautious.  But, it is very easy method for reading into your transaction log files.