SQL Server Audit: A Production Guide to Setup, Targets and Monitoring

After a security incident, the first questions are almost always the same: who made this change, when, from which machine, and what else did they touch? Audits for GDPR, PCI DSS, ISO 27001 or internal controls ask similar questions, this time with an expectation of evidence. SQL Server Audit lets you answer them from the database engine itself, without an additional product.

The value of an audit, however, comes from how it is configured rather than from the fact that it is switched on. An audit that tracks the wrong events, quietly stops recording when the disk fills up, or sits in a folder anyone with sysadmin rights can delete won’t give you the assurance you need when it matters. This guide covers the architecture of SQL Server Audit, target and failure-behavior choices, which events to capture, the detail that breaks auditing in Always On environments, and how to read the records and get them into a SIEM, all from a production point of view.

How SQL Server Audit Works

SQL Server Audit is built on the Extended Events infrastructure and has three components:

• Server audit: the instance-level object that defines where records are written (the target) and what happens when they can’t be written. An instance can have several server audits.
• Server audit specification: defines server-level action groups such as logins, permission changes and backups. Each server audit can have one.
• Database audit specification: defines events in a specific database, for example SELECT or UPDATE on a table. Each database can have one per server audit.

A server audit is created in a disabled state and records nothing until it is explicitly enabled along with its specifications. Server-level auditing is available in every edition of SQL Server. Database-level auditing is supported in all editions, including Standard, from SQL Server 2016 SP1 onwards; before that it was limited to Enterprise.

What Auditing Is Not

An audit records who did what, when and from where; it does not record the old and new values of changed data. When the query processor parameterizes a query, the audit record may also show parameter names instead of actual values. It’s worth setting expectations accordingly and combining auditing with other mechanisms where needed:

Choosing a Target: File or Windows Event Log?

Audit records can be written to one of three targets. The choice directly determines how well the records are protected and how they are collected.

To use the Security log, add the SQL Server service account to the “Generate security audits” right in the local security policy (secpol.msc) and enable the “Application Generated” subcategory for both success and failure:

auditpol /set /subcategory:"application generated" /success:enable /failure:enable

In practice, the setup we use most often is a file target for high-volume events and the Security log for low-volume but critical security events. With a file target, the files must be moved off the server, either by a SIEM agent or a regular copy job.

File Size, Retention and Failure Behavior

Four settings on a file target determine how far back the audit reaches and how it behaves when the disk fills up:

• MAXSIZE: the maximum size a single audit file can reach. The default is UNLIMITED, in which case the file grows until the disk is full, so always set an upper limit.
• MAX_ROLLOVER_FILES: the number of files kept in addition to the current one. When the limit is reached, the oldest file is deleted. For example, 256 MB and 40 files give you a rolling window of roughly 10 GB.
• MAX_FILES: an alternative to MAX_ROLLOVER_FILES. When the limit is reached, old files are not deleted and audited actions start failing. It is meant for environments where records must never be lost.
• QUEUE_DELAY: how long records may wait in memory before being written. The default is 1000 milliseconds. A value of 0 means synchronous delivery, which makes every audited action wait for its record to be written; on busy systems we recommend keeping the default.

The most important decision is ON_FAILURE, which determines what SQL Server does when it can’t write to the audit target, for example when the disk is full or a network share is unreachable:

• CONTINUE (default): the database keeps running, but no records are produced until the problem is fixed. Suitable where uninterrupted operation matters more than a complete record.
• FAIL_OPERATION: actions that would generate audit events fail, while other actions continue. If only sensitive tables are audited, for instance, access to those tables is blocked while records can’t be written.
• SHUTDOWN: the SQL Server instance shuts down if the audit record can’t be written. Meant for environments where a complete audit trail matters more than availability, and the login creating it needs the SHUTDOWN permission.

What Should You Audit?

Trying to audit everything produces terabytes of records nobody reads and a measurable performance cost. A better approach is to split events into separate audits by volume and information value.

1. Security baseline: always on, on every server

Failed logins, permission and role changes, login and user management, backup and restore operations, and changes to the audit configuration itself are low-volume but high-value events. We recommend extending the baseline from our SQL Server security hardening guide as follows:

USE master;
CREATE SERVER AUDIT [Audit_Security]
    TO FILE (FILEPATH = N'E:\SQLAudit\', MAXSIZE = 256 MB, MAX_ROLLOVER_FILES = 40)
    WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

CREATE SERVER AUDIT SPECIFICATION [AuditSpec_Security]
    FOR SERVER AUDIT [Audit_Security]
    ADD (FAILED_LOGIN_GROUP),
    ADD (LOGIN_CHANGE_PASSWORD_GROUP),
    ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
    ADD (SERVER_PERMISSION_CHANGE_GROUP),
    ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
    ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP),
    ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
    ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
    ADD (DATABASE_PERMISSION_CHANGE_GROUP),
    ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
    ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
    ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
    ADD (DATABASE_CHANGE_GROUP),
    ADD (SERVER_OBJECT_CHANGE_GROUP),
    ADD (SERVER_STATE_CHANGE_GROUP),
    ADD (BACKUP_RESTORE_GROUP),
    ADD (AUDIT_CHANGE_GROUP)
    WITH (STATE = ON);

ALTER SERVER AUDIT [Audit_Security] WITH (STATE = ON);

SUCCESSFUL_LOGIN_GROUP is deliberately left out. With connection pooling, every pooled connection raises an event, so on busy systems this group can produce millions of records a day. If successful logins need to be tracked, a separate, filtered audit that excludes application logins is the better option.

2. Schema changes

Tracking changes to tables, views and stored procedures answers the “who changed this procedure last night?” question. SCHEMA_OBJECT_CHANGE_GROUP and DATABASE_OBJECT_CHANGE_GROUP cover this. Nightly index maintenance also raises ALTER INDEX events, so excluding the service account that runs maintenance with a WHERE clause cuts the noise considerably:

USE master;
CREATE SERVER AUDIT [Audit_SchemaChanges]
    TO FILE (FILEPATH = N'E:\SQLAudit\', MAXSIZE = 256 MB, MAX_ROLLOVER_FILES = 20)
    WITH (ON_FAILURE = CONTINUE)
    WHERE ([server_principal_name] <> N'CORP\svc_sqlagent');

CREATE SERVER AUDIT SPECIFICATION [AuditSpec_SchemaChanges]
    FOR SERVER AUDIT [Audit_SchemaChanges]
    ADD (SCHEMA_OBJECT_CHANGE_GROUP),
    ADD (DATABASE_OBJECT_CHANGE_GROUP)
    WITH (STATE = ON);

ALTER SERVER AUDIT [Audit_SchemaChanges] WITH (STATE = ON);

The WHERE clause is defined on the server audit and applies to every specification attached to it. Most fields returned by sys.fn_get_audit_file can be filtered; event_time, file_name and audit_file_offset are the exceptions. action_id and class_type must be used with numeric values, not strings, in a filter.

3. Access to sensitive data

To record who accesses tables that hold personal or financial data, use a database audit specification. The audited object can be a table, a schema or the whole database:

USE master;
CREATE SERVER AUDIT [Audit_SensitiveData]
    TO FILE (FILEPATH = N'E:\SQLAudit\', MAXSIZE = 256 MB, MAX_ROLLOVER_FILES = 40)
    WITH (ON_FAILURE = CONTINUE)
    WHERE ([server_principal_name] <> N'CustomerApp');
ALTER SERVER AUDIT [Audit_SensitiveData] WITH (STATE = ON);
GO

USE CustomerDB;
CREATE DATABASE AUDIT SPECIFICATION [AuditSpec_SensitiveData]
    FOR SERVER AUDIT [Audit_SensitiveData]
    ADD (SELECT, INSERT, UPDATE, DELETE ON OBJECT::dbo.Customer BY public),
    ADD (SELECT ON SCHEMA::Finance BY public),
    ADD (EXECUTE ON OBJECT::dbo.usp_ExportCustomers BY public)
    WITH (STATE = ON);

BY public covers every user in the database. If you only want to track administrator activity, remember that members of sysadmin appear as the dbo user in every database, so use BY dbo. The WHERE clause in the example excludes the application login: the millions of accesses an application makes to these tables would put a serious load on both disk and performance, and tracking application access through the application’s own logs is usually more realistic.

From SQL Server 2022 onwards, SENSITIVE_BATCH_COMPLETED_GROUP records queries that touch columns labeled through data classification. Instead of defining tables one by one, it relies on the classification labels. To capture queries coming from other databases as well, define the group at the server level rather than the database level.

4. Queries run by non-application users

Queries run directly by DBAs, developers and reporting users are the records most needed in an incident investigation. From SQL Server 2022 onwards, BATCH_COMPLETED_GROUP can record the full batch text. Building this audit with a WHERE clause that excludes application logins keeps the volume under control; you’ll find a sample configuration in step 7 of our security hardening guide.

Auditing in Always On: The AUDIT_GUID Detail

There’s a detail that is often missed in Always On availability groups. A database audit specification is stored inside the database and travels with it to the secondary replicas. The server audit and the server audit specification, however, are instance-level objects and are not synchronized between replicas. A database audit specification is bound to its server audit by GUID, not by name. If the secondary replica has no server audit with the same GUID, database-level auditing keeps running after a failover without producing a single record.

So create the server audit on the primary replica first, read its GUID, and then create it with the same GUID on every secondary replica:

-- On the primary replica:
SELECT name, audit_guid FROM sys.server_audits WHERE name = N'Audit_SensitiveData';

-- On every secondary replica, with the same GUID:
CREATE SERVER AUDIT [Audit_SensitiveData]
    TO FILE (FILEPATH = N'E:\SQLAudit\', MAXSIZE = 256 MB, MAX_ROLLOVER_FILES = 40)
    WITH (ON_FAILURE = CONTINUE, AUDIT_GUID = '5d0a8a5e-6f3c-4c1b-9a51-2f7e3b9c1d42')
    WHERE ([server_principal_name] <> N'CustomerApp');
ALTER SERVER AUDIT [Audit_SensitiveData] WITH (STATE = ON);

Reading Audit Records

Records in a file target are read with sys.fn_get_audit_file. Using it requires CONTROL SERVER on SQL Server 2019 and earlier, and VIEW SERVER SECURITY AUDIT on SQL Server 2022 and later. Three things to keep in mind: event_time is stored in UTC, action_id is a four-character code, and the client_ip and application_name columns are available from SQL Server 2017 onwards. The query below returns the last 24 hours in the server’s local time and shows readable action names:

SELECT
    SWITCHOFFSET(CAST(af.event_time AS datetimeoffset),
                 DATENAME(TZOFFSET, SYSDATETIMEOFFSET())) AS event_time_local,
    da.name AS action_name,
    af.succeeded,
    af.session_server_principal_name AS original_login,
    af.server_principal_name AS login_name,
    af.client_ip,
    af.application_name,
    af.database_name,
    af.schema_name,
    af.object_name,
    af.statement
FROM sys.fn_get_audit_file(N'E:\SQLAudit\Audit_Security_*.sqlaudit', DEFAULT, DEFAULT) AS af
LEFT JOIN (SELECT DISTINCT action_id, name FROM sys.dm_audit_actions) AS da
    ON da.action_id = af.action_id
WHERE af.event_time >= DATEADD(HOUR, -24, SYSUTCDATETIME())
ORDER BY af.event_time DESC;

The difference between session_server_principal_name and server_principal_name matters when investigating sessions that switch identity with EXECUTE AS: the first shows the login that opened the session, the second the identity in effect when the action ran. Also keep in mind that the function reads every matching file on each call and the WHERE clause is applied afterwards. With large audit folders these queries slow down, so for regular analysis it’s more efficient to load the records into a central table.

Loading records into a central table

sys.fn_get_audit_file can start reading from a specific file and offset. If the last file and offset read are kept in a control table, a SQL Server Agent job can load only the new records:

DECLARE @file nvarchar(260), @offset bigint;

SELECT @file = last_file, @offset = last_offset
FROM AuditArchive.dbo.ReadState
WHERE audit_name = N'Audit_Security';

SELECT event_time, action_id, succeeded, server_principal_name, client_ip,
       application_name, database_name, object_name, statement, file_name, audit_file_offset
INTO #new
FROM sys.fn_get_audit_file(N'E:\SQLAudit\Audit_Security_*.sqlaudit', @file, @offset);

INSERT INTO AuditArchive.dbo.AuditRecord
SELECT * FROM #new;

SELECT TOP (1) @file = file_name, @offset = audit_file_offset
FROM #new
ORDER BY event_time DESC, audit_file_offset DESC;

IF @@ROWCOUNT > 0
    UPDATE AuditArchive.dbo.ReadState
    SET last_file = @file, last_offset = @offset
    WHERE audit_name = N'Audit_Security';

Schedule the job so that files are read before rollover deletes the oldest one; otherwise the read fails when the starting file can’t be found. Microsoft recommends generating audit reports from a separate instance that only audit administrators and readers can access. In that case, copy the audit files to a share with restricted permissions that the reporting instance can reach, and apply the same approach there.

Protecting the Audit Itself

The weakest point of any audit is that the most privileged people can change it. Every login in the sysadmin role can alter or disable audit components, and db_owner members can alter database-level specifications. You can’t remove that risk, but you can make it visible:

• Get the records off the server. A Security log target, or a SIEM agent collecting the files, makes deleting records on the server pointless.
• Restrict permissions on the audit folder. The SQL Server service account and audit administrators need read and write access; audit readers need read access only.
• Record who reads the audit files. An audit defined on master.sys.fn_get_audit_file shows who looks at the records through SQL Server.
• Monitor audit status. Add the query below to a scheduled job or your monitoring tool and alert on any audit whose status isn’t STARTED.

SELECT name, status_desc, status_time, audit_file_path, audit_file_size
FROM sys.dm_server_audit_status;

On the SIEM side, we recommend alerts for at least the following: a new sysadmin membership, an audit being disabled or its specification changed, a burst of failed logins in a short period, access to sensitive tables outside business hours, and an unexpected RESTORE or backup.

Seeing the User Behind a Shared Application Login

Most business applications connect to the database through a single login. Audit records then show only that application login; the actual person who opened a customer record on screen is unknown to the database. If you can change the application side, adding a sp_audit_write call to the stored procedures behind critical actions carries the application user into the audit record:

-- USER_DEFINED_AUDIT_GROUP must be part of the relevant specification
-- In the application, @AppUser and @CustomerId come from the procedure's parameters
DECLARE @AppUser sysname = N'jdoe', @CustomerId int = 42;

DECLARE @info nvarchar(4000) =
    CONCAT(N'app_user=', @AppUser,
           N'; action=ViewCustomerRecord; customer_id=', @CustomerId);

EXEC sys.sp_audit_write
    @user_defined_event_id = 1001,
    @succeeded = 1,
    @user_defined_information = @info;

If USER_DEFINED_AUDIT_GROUP hasn’t been added to a server or database audit specification, sp_audit_write calls are silently ignored. After configuring it, write a test record and read it back to confirm it works.

Implementation Checklist

• Audit files live on a disk separate from data and log files, and that disk is monitored.
• Every audit has a MAXSIZE and a file count, and the ON_FAILURE choice was made deliberately.
• The security baseline is enabled on every server and includes AUDIT_CHANGE_GROUP.
• Sensitive tables and non-application users are tracked by separate, filtered audits.
• On Always On replicas, server audits were created with the same GUID, and recording after a failover has been tested.
• Records are shipped off the server to a SIEM or a separate archive instance, with alerts on critical events.
• sys.dm_server_audit_status is checked regularly.