Who’s Updated My Rows?! Interrogating the Transaction Logs

I’m pretty much positive that we’ve all been here at one time or another.  We’re sitting at our desk watching Klaus’ latest SQL Quickie when a red faced colleague comes marching up to our desk and splutters out, in their rage “Someone’s updated all my rows, can you find out who did it?”.  Now usually, unless there’s some sort of auditing in place the answer would be a quick no.

But then I got thinking to myself, is there something that we can do?  There is somewhere that stores everything that happens in a transaction after all, isn’t there?  The transaction log of course.  But first things first, lets have a look at what’s happened.

Somehow,  someone out there has managed to update all first names in our person table to ‘Bob’.  Obviously forgotten their WHERE clause… because you’ve never done that, right?!

So it’s looking like things are in a bad way, obviously we could go to a backup and get the old values back but that’s never going to tell us who made the change.  So that transaction log again, how do we actually go about getting our hands dirty and having a look at it.

Well there’s a nice little undocumented function called fn_dblog.  Let try giving that a go and see what we get back. By the way, the two parameters are the first and last LSNs that you want to look between.  Leaving them as NULL with return the entire log.

SELECT *
FROM fn_dblog(NULL,NULL)

Wow, there’s a hell of a lot of information in the table so where do we start?

Well the first thing that we need to do is find any changes that have been made to the table that we’re worried about, in our case that’s Person.Person.

One piece of information that dn_dblog does give us is the partition ID.  Knowing that we can easily tweek our query above to only return rows that contain the partition ID(s) of the Person table.  Let’s have a look at that….

SELECT Operation, Context, [Transaction ID], OBJECT_NAME(object_id), [Begin Time], [Transaction SID]
FROM fn_dblog(NULL,NULL)
JOIN sys.partitions ON fn_dblog.PartitionId = partitions.partition_id
WHERE OBJECT_NAME(object_id) = 'person'
AND index_id = 1

So there we have it, all the modifications that affect the table that we’re interested in.  But hold on a minute, there are a couple of crucial pieces of information that seem to be missing and that’s the Begin Time of the transaction and the Transaction SID.

For some reason that’s beyond me but that I’m sure there’s a very good reason for, those pieces of information aren’t recorded with the individual operations but there is somewhere that they are recorded and that’s during the begin and commit sections of a transaction.  We have got the transaction IDs now so with another tweak to our code it shouldn’t be too difficult to find the corresponding begin transaction lines and in turn the SID of the login that performed the actions.

WITH CTE AS
(SELECT Operation, Context, [Transaction ID], OBJECT_NAME(object_id) ObjectName, [Begin Time], [Transaction SID]
FROM fn_dblog(NULL,NULL)
JOIN sys.partitions ON fn_dblog.PartitionId = partitions.partition_id
WHERE OBJECT_NAME(object_id) = 'person'
AND index_id = 1)

SELECT CTE.Operation, CTE.Context, CTE.[Transaction ID], CTE.ObjectName, fn_dblog.[Begin Time], fn_dblog.[Transaction SID], SUSER_SNAME(fn_dblog.[Transaction SID]) AS UserName
FROM CTE
JOIN fn_dblog(NULL,NULL) ON CTE.[Transaction ID] = fn_dblog.[Transaction ID]
WHERE fn_dblog.Operation = 'LOP_BEGIN_XACT'

So there we have it folks, we can see when modifications were made to our table and who made them.