Just before Christmas we had a problem with an application database where rows were being mysteriously deleted. Unfortunately the table in question is written to by 20-30 stored procedures, so there was no single point in the application where I could put a trace.

Instead, I wrote a quick generic script that would automatically create the necessary triggers to log all INSERT, UPDATE and DELETE commands on a table of your choosing (or just generate the script).

For example, every time someone modified data in the HR.Employees table, a row would be written to HR._Employees_Audit with all the new values, time stamp, command, and name of the user who did it.

You can download it here: sql-server-auto-audit.sql