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

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.