0

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.

Ken White
  • 123,280
  • 14
  • 225
  • 444
Pushpin29
  • 21
  • 3

1 Answers1

0

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>
Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • Thank you @Ben Thul. Semi off topic, but would you happen to know how to capture when an Audit definition has been altered? Can't use a trigger - Modify_date in sys.database_audit_specifications doesn't seem to update when using an Alter statement. I'm struggling to find a sys table to help me with this. Just want to know if/when someone alters any of my server / db audits, not really concerned with the who just yet. – Pushpin29 Apr 19 '22 at 18:50
  • *Quis custodiet ipsos custodes?* An Audit can be set up to monitor changes to Audits. run `select * from sys.dm_audit_actions where class_desc like '%audit%'` to see all of your possibilities. Note the `covering_parent_action_name` - you can audit for that and get all of the covered actions with one action. For example, if you audit for `AUDIT_CHANGE_GROUP` in a server audit specification, you'll get any `create`, `alter`, or `drop` operations. – Ben Thul Apr 20 '22 at 17:04