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.
