Finding Out Whodunnit Using The Transaction Log

David Fowler shows us how to figure out which user made a bad data change when you don’t have auditing mechanisms in place:

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.

This is great unless you have connection pooling and the problem happened through an application.  In that case, the returned username will be the application’s username.

Related Posts

Default Schemas In SQL Server

Daniel Hutmacher looks at specifying default schemas on a database: If your user is a database owner, (i.e. is a member of the db_owner group or has CONTROL permissions on the database) the default schema will always be dbo. This is something you can’t change. So if your legacy application needs quasi-administrative privileges in the database, you can’t make it […]

Read More

Window Function Basics

Doug Kline has a new series on window functions.  First, he looks at differences between RANK, DENSE_RANK, and ROW_NUMBER: — Quick! What’s the difference between RANK, DENSE_RANK, and ROW_NUMBER? — in short, they are only different when there are ties… — here’s a table that will help show the difference — between the ranking functions […]

Read More


September 2017
« Aug Oct »