SQL Server 2012/2014: Extended Events to audit statements

Recently I had a request to set up audit for a SQL Server database to capture any DDL statements and any updates to specify tables. For this I could not run a profiler trace all the time so upon some investigation, I decided to use Extended Events to capture these events.

CREATE EVENT SESSION [DDL_Changes] ON SERVER 
ADD EVENT sqlserver.sp_statement_completed(
    ACTION(package0.event_sequence,sqlos.task_time,sqlserver.client_app_name,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_sid,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username)
    WHERE ((([sqlserver].[username]=N'Theusername') AND ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER TABLE%'))) AND ([sqlserver].[database_name]=N'YourDBName'))) 
ADD TARGET package0.event_file(SET filename=N'H:\MSSQL$MSSQLSERVER\AuditLogs\DDL_Changes.xel',max_file_size=(100),max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

The above CREATE statement is to capture any ALTER TABLE commands that are executed against the database. You can explore to check how you can capture multiple types of SQL_TEXT events.

I would open the Extended Event under 'Management' in SSMS and edit the properties on this event.
You can then right click on the event and start it, to capture all the events.

To can read from the Extended event using the following commands
1) First read the xel file and write the contents to a temp table

IF OBJECT_ID('tempdb..#ExEvent') IS NOT NULL DROP TABLE #ExEvent

SELECT IDENTITY(INT,1,1) AS RowId, object_name AS event_name, CONVERT(XML,event_data) AS event_data
    INTO #ExEvent
FROM sys.fn_xe_file_target_read_file(N'H:\MSSQL$MSSQLSERVER\AuditLogs\DDL_Changes.xel', null, null, null);

2) Then read from this temporary table.

SELECT  RowId, event_name, [sql_text], [username], [database_name], [transaction_id],  [task_time]                  
    FROM (
            SELECT RowId
                    , event_name
                    , T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')AS att_name
                    , T2.Loc.query('.').value('(/action/value)[1]', 'varchar(max)')AS att_value
            FROM   #ExEvent
            CROSS APPLY event_data.nodes('/event/action') as T2(Loc)
            WHERE T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')
                IN ('sql_text', 'username', 'database_name', 'transaction_id',  'task_time')

        ) AS SourceTable
            PIVOT(
                MAX(att_value)
                FOR att_name IN ([sql_text], [username], [database_name], [transaction_id],  [task_time])

        ) AS PivotTable 
order by  [task_time]  desc


You can open the properties of the Event and then play with it to change some of these settings. The most important to remember how you read from the file.