Auditing via the T-log

By | January 19, 2016

A client of mine recently wanted to find out who deleted all the records from a table being used in production. This is an issue that we have all come across at some point in our careers. Usually the stage just before we finally implement audits or triggers to prevent these things from happening in future. Now usually it would be great to have and audit or to be able to find the required details from within the default trace or if you’re very lucky from the sys.dm_exec_connections DMV. But we are almost always guaranteed to be unlucky when it comes to tracing the culprit. So we resort to the T-log. Naturally it makes sense to look at the t_log since it logs for every DML operation that happened. So we are bound to find something useful in it.

In this particular case we first try

select * from sys.fn_dbLog(null,null)

This undocumented DMF reads the T-log and represents the data within the T-log in an easy to understand grid format. I mainly use this DMF to understand the internal workings on Recovery models and backups and restores but it can be used pretty much for all SQL internal investigation. The below screenshot shows an example of the DMF being used. Notice that everything we need to identify who made the change and to which table they made it is highlighted in red.

Auditing via Tlog

Make sure to map the user to the right transaction. However as is the case with most companies we take backups that cause the T-log to rollover and then we lose the data available above. In these cases there is another DMF that can be used to read the data directly off the t-log backups. The below script demonstrates the example where sys.fn_dump_dblog

Auditing via Tlog

Next we delete from the table and track the changes from the backup.

Auditing via Tlog

As you can see from the above script the DMF reads the T-log to identify the records that are modified and the user responsible or it, however there are a few challenges to using either of the above DMF, the first being you need to know the exact time when the problem occurred otherwise it’s just too much info returned from the DMF to effectively troubleshoot. The second thing is the username returned only works if you’re using Windows Authentication. If you’re using SQL authentication then any user can login using that account and as a result it won’t be possible to pin the blame. Basically we need to ensure that we complete the security circle by implementing principle of least privileges and auditing to protect our data.

PS:- sorry for the screenshots but the plugin I use for WP throws one issue after the other with long T-SQL code. Any suggestions for a better plugin would be greatly appreciated. Currently using Developer Formatter

References
http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/

Leave a Reply