Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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).

share|improve this question
2  
Wait, you mean you don't use sa to do all the work like 99.999% of all SQL Server apps out there? runs –  Ignacio Vazquez-Abrams Jun 21 '11 at 23:34
    
@Ignacio - I'm in the process of switching our apps from using sa to trusted connections. It's a friggin mission. So... many... permissions... and... groups... –  Mark Henderson Jun 22 '11 at 1:27
add comment

3 Answers

up vote 0 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.