SQL Audits

1. sys.server_audits

• What it is:
  Lists all server-level audit objects.
  An audit is the top-level object that defines:

  • Where to write logs (file, Windows event log, security log).

  • Audit name, status (started/stopped), and file path.

• Usage:

  SELECT name, audit_guid, type_desc, on_failure_desc, is_state_enabled
  FROM sys.server_audits;
  

   This tells you what audit sinks exist and whether they’re active.

---

2. sys.server_audit_specifications

• What it is:
  Links server-level actions (like LOGIN events, role changes, etc.) to a server_audit.
  Example: “Audit all failed logins → send to Audit1”.

• Usage:

  SELECT name, audit_action_name, audited_result, is_state_enabled
  FROM sys.server_audit_specifications;
  

   Shows what is being captured at server scope.

---

3. sys.database_audit_specifications

• What it is:
  Similar to sys.server_audit_specifications, but per-database.
  Captures things like SELECT, INSERT, EXECUTE inside a database.

• Usage:

  SELECT name, database_principal_id, audit_action_name, is_state_enabled
  FROM sys.database_audit_specifications;
  

   Lets you see what actions are logged inside a specific DB.

---

4. sys.traces

• What it is:
  Lists SQL Server Profiler traces (old-school tracing, predecessor of Extended Events).
  Default trace is usually enabled to capture schema changes, errors, etc.

• Usage:

  SELECT id, path, is_default, start_time, stop_time
  FROM sys.traces;
  

   Useful for checking if the default trace is running.

---

5. sys.dm_xe_sessions

• What it is:
  Lists running Extended Events (XE) sessions.
  XE replaced Profiler/Trace for lightweight performance monitoring and auditing.

• Usage:

  SELECT name, status, start_time
  FROM sys.dm_xe_sessions;
  

   See what XE sessions are active (like system_health).

---

6. sys.server_file_audits

• What it is:
  Lists only file-based server audits (subset of sys.server_audits).
  Includes info about file path, rollover, max size, queue delay.

• Usage:

  SELECT audit_id, file_path, max_file_size, max_rollover_files
  FROM sys.server_file_audits;
  

   Useful when your audit is writing to .sqlaudit files.

---

 Putting it together

• sys.server_audits → Defines audit targets (where to log).

• sys.server_audit_specifications → Defines what server-level actions to audit.

• sys.database_audit_specifications → Defines what database-level actions to audit.

• sys.traces → Shows legacy Profiler traces.

• sys.dm_xe_sessions → Shows Extended Events sessions (modern tracing).

• sys.server_file_audits → Details of file-based audit storage.

---