8. DATABASE AUDIT

Question

Tell me all I need to know about audits in MSSQL and some example code

Answer 1

SQL Server Audit is a built-in feature that tracks and logs server-level and database-level actions to a chosen target (file or Windows event log). It is used for security, compliance (for example GDPR), and forensic analysis of activity in SQL Server.

Key concepts

1. Audit

• Instance-level object that defines:
  • What audit specification(s) can send events to it.
  • Where the audit records are written (file, Windows Application log, or Windows Security log).
• Created in a disabled state; must be enabled before it starts receiving events.

2. Server Audit Specification

• Instance-level object that belongs to a specific audit.
• Collects server-level action groups (for example, login events, server configuration changes).
• Only one server audit specification per audit.
• Uses predefined audit action groups; these are raised by Extended Events.

3. Database Audit Specification

• Database-level object that belongs to a specific audit.
• One database audit specification per database per audit.
• Collects database-level audit actions or action groups (for example, SELECT, INSERT, UPDATE on tables, or schema-level actions).
• Must not include server-scoped objects (such as system views) when created in a user database; to audit server-scoped objects, use a database audit specification in master.

4. Target

• Where audit records are written:
  • Binary file.
  • Windows Application event log.
  • Windows Security event log (more secure, higher permission requirement).
• Logs must be reviewed and archived periodically to ensure sufficient space.

5. Audit records

• Each audit record contains fields such as:
  • event_time: when the action fired.
  • sequence_number: sequence within a large record.
  • action_id: ID of the audited action.
  • succeeded: whether the permission check succeeded.
  • permission_bitmask, is_column_permission, and other metadata.
• Records can be read via:
  • Log File Viewer in SSMS (for file targets).
  • fn_get_audit_file function (for file targets).
  • Windows Event Viewer (for Application/Security logs).

6. Permissions

• ALTER ANY DATABASE AUDIT is required to create database audit specifications and bind them to an audit.
• CONTROL SERVER or ALTER ANY DATABASE AUDIT (or membership in sysadmin) is required to view database audit specifications.

7. General workflow

1. Create an audit and define its target.
2. Create a server audit specification and/or database audit specification that maps to the audit and enable the specification(s).
3. Enable the audit.
4. Read and analyze audit events from the chosen target.

Example: create a server audit and database audit specification (T‑SQL)

Create a server audit that writes to a file and enable it:

USE master;
GO

-- Create the server audit.
CREATE SERVER AUDIT Payroll_Security_Audit
TO FILE (
    FILEPATH = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL13.MSSQLSERVER\\MSSQL\\DATA'
);
GO

-- Enable the server audit.
ALTER SERVER AUDIT Payroll_Security_Audit
WITH (STATE = ON);
GO

Create a database audit specification that audits SELECT and INSERT on a specific table by a specific principal and enable it:

USE AdventureWorks2022;
GO

-- Create the database audit specification.
CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables
FOR SERVER AUDIT Payroll_Security_Audit
    ADD (SELECT, INSERT ON HumanResources.EmployeePayHistory BY dbo)
WITH (STATE = ON);
GO

Example: high-level pattern from documentation

• A server audit is created (for example, Payroll_Security_Audit or DataModification_Security_Audit).
• A database audit specification is created for that audit, such as:
  • Audit_Pay_Tables: audits SELECT and INSERT on HumanResources.EmployeePayHistory by a specific user or role.
  • Audit_Data_Modification_On_All_Sales_Tables: audits INSERT, UPDATE, DELETE on all objects in the Sales schema for a specific role (for example, SalesUK).

Operational notes

• Database audit specifications are created disabled; explicitly enable them with WITH (STATE = ON).
• Database audit specifications reside in the database where they are created (except tempdb).
• tempdb and temporary tables are not audited; batch-completed action groups may capture statements but object names for temp objects may not be populated correctly.
• For GDPR and similar regulations, SQL Server Audit provides persistent, fine-grained tracking of access and changes, supporting incident investigation and reporting.

---

References:

• Create a server audit and database audit specification
• CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)
• SQL Server Audit (Database Engine)
• SQL Server Audit Records
• Take an Access excursion through SQL Server