Extended Events for Server/Database Audit

513 views Asked by At

I am wanting to know if anyone is aware if there is an Extended Event I could utilize to detect if a SQL server/database audit definition has been altered, created, deleted, etc. Currently I am utilizing SQL server/database audits, but am being introduced to Extended Events.

A lot of what I'm googling is related to XE vs. SQL Audits. Not so much on how to use XE to monitor SQL audits.

Looking for a way to "Audit my Audits".

Thank you.

1

There are 1 answers

2
Ben Thul On

Any Audit will track its own starts and stops under the Audit Session Changed (AUSC) event. I created an Audit on my local instance and merely enabled/disabled it and then ran the following query:

select actions.name, event_time, additional_information
from sys.fn_get_audit_file('c:\temp\TestAudit*', DEFAULT, DEFAULT) as events
join sys.dm_audit_actions as actions
    on actions.action_id = events.action_id;

Here's what I see:

name    event_time  additional_information
AUDIT SESSION CHANGED   2022-04-10 16:41:58.7244182 <action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><session><![CDATA[TestAudit$A]]></session><action>event enabled</action><startup_type>manual</startup_type><object><![CDATA[audit_event]]></object></action_info>
AUDIT SESSION CHANGED   2022-04-10 16:42:09.2291167 <action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><session><![CDATA[TestAudit$A]]></session><action>destroyed</action></action_info>
AUDIT SESSION CHANGED   2022-04-10 16:42:09.2291167 <action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><session><![CDATA[TestAudit$A]]></session><action>event disabled</action><object><![CDATA[audit_event]]></object></action_info>