Tracy Boggiano wants to figure out who keeps taking her lunch out of the company refrigerator:
I had a problem at work recently where a record was getting updated, and no one knew where or what was updating the record. Our team discussed the best way to try to figure out what was happening. The situation was if a record would be updated to active and within a ten-minute window, the record would be set back to inactive. The system allows ad-hoc statements to run against and since it was to only a certain table, I suggested we set up a SQL Audit to track UPDATEs to the table. The code for this is fairly simple, but since most of my colleagues don’t have exposure to SQL Audit, I figured a blog post would benefit others.
So, in this case, we are creating a Server SQL Audit that will write to D:\SQL Audit, so make sure that path exists. Then a Database Server Audit Specification to track any UPDATEs that happen to the table. Now, keep in mind I choose the method over running a server-side Trace or Extended Events because I knew it would capture everything without me having to worry about setting up anything else put these commands. An important part of this is where I specify “public”. That tells the audit to capture anybody that is updating the table. If you want to look for a certain user or even maybe someone part of a role, you could specify that instead.
Click through for the auditing script. I wish this type of information were a lot easier to get, especially for longer-term audits. I end up creating metadata columns (created/modified user, created/modified date) but that gives limited information and requires all calling code play along.