How to Audit user DML with SQL Server Extended Events

Question: What methods are available to audit the DML activity of a user. I need to track a third party developer and record the DML statements they execute on a SQL Server data? 

The data needs to be recorded and made available for a regular review.

Answer: There are many ways to skin the audit cat. Since SQL Server 2008 the DBA life has become easier with the introduction of Extended events , as applied to auditing. Before I  explain of how to set up Extended Events let’s review some other methods

Manual auditing –  refers to a home grown method of auditing. For example, there may be some extra code added which records the code executed into a separate table or log file. The main disadvantage of manual auditing is it can be error prone and time consuming to set up

SQL Server triggers – This was a popular method prior to Extended Events. It’s very easy to set up , but in a heavy workload OLTP database can cause performance issues, due to extra overhead of executing triggers, Read more on SQL Server Triggers and Performance - SQL Server DBA

Reading transaction logs – As part of the Recovery process SQL Server tracks all changes made. There is minimal overhead using this method, but can be complicated.

SQL Server Profiler and SQL Server traces –  I prefer to use Profiler for troubleshooting. It’s a great tool with many customisable features. Some skill is required to read and oraganise the output. But there are third party tools which present the data in a more readable format

SQL Server Extended Events – A straight forward set up procedure makes it easy to set up . It’s main disadvantage is you can’t easily record the new or old values used in updates, which is one of the strengths of the SQL Trigger option.

For a user audit where I need to record DML statements SQL Server Extended Events offers decent flexibility. Another disadvantage is if multiple instances are required  to be audited it cant be completed by default. Some planning is required on how audit data  will be manage

Here is a sample code which takes you through a CREATE DATABSE and configuring a server audit, database server audit specification and reading the audit output.

These are basic specifications . There is plenty of refining the specifications at an object schema, object class, database and user level

Download Create a sql server audit with Extended Events

Read More on auditing

SQL Server - Database Server Security Audit Process - SQL Server ...

SQL Server – Audit Backup\Restore Event with SQL Default trace ...