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….

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.

David Fowler

David is a DBA with over 14 years production experience of SQL Server, from version 6.5 through to 2016. He has worked in a number of different settings and is currently the technical lead at one of the largest software companies in the UK.

Adrian Buckman

After working in the motor trade for over 11 years Adrian decided to give it all up to persue a dream of working in I.T. Adrian has over 4 years of experience working with SQL server and loves all things SQL, Adrian currently works as a Database Administrator for one of the UK’s Largest Software Companies