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

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 01:27

score 1 · Answer 1 · answered Nov 18 '13 at 18:59

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

score 0 · Accepted Answer · answered Jun 21 '11 at 23:50

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.

score 0 · Answer 3 · answered Jun 22 '11 at 00:10

0

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.

answered Jun 22 '11 at 00:10

SqlACID

2,176
18
18

DML Auditing ("Who did inserts?")

3 Answers3