DML Auditing ("Who did inserts?")

Question

Does an out-of-the-box newly created database (in SQL Server 2008) keep any DML audit records, such as:

• User 'George' did an insert on table 'Alpha' on 6/20/2011
• User 'Fred' did a delete on table 'Beta' on 6/21/2011

If yes, how do I access that information? If not, what is the easiest way to turn it on (I presume these are the audit options).

Answer 1

The transaction log would have this if the database is if full recovery mode and either the log has not been backed up, or the log backups are available.

There's a couple of third party products such as LiteSpeed for SQL Server which can view the transaction log.

If the database is in simple recovery mode this data isn't kept very long.

Answer 2

You can manually create appropriate DML triggers, but that requires manual set up of auditing repository too. On the other hand, the process can be automated using ApexSQL Audit, an auditing tool for SQL Server databases which captures data changes that have occurred on a database including the information on who made the change, which objects were affected, when it was made, as well as the information on the SQL login, application and host used to make the change. It stores all captured information in a central repository and exports them in print friendly formats The technology which ApexSQL Audit uses for auditing is based on triggers. These are created using its visual interface, without any need for T-SQL knowledge.

Disclaimer: I work as a Product Support Engineer at ApexSQL

Answer 3

The link you mention has good step-by-step instructions, it will do exactly what you want, just make sure you don't leave the audit file size unlimited, and I would recommend capping the size in the MB's, otherwise they take forever to open.