← Back to blog

SQL Server Auditing Guide: Enable, Configure and Review

November 14, 2026
SQL Server Auditing Guide: Enable, Configure and Review

SQL Server Audit is the built-in feature that records who logged in, who changed what and who read which table. To use it, you create a server audit, attach a server audit specification and a database audit specification, turn the audit on, and review the output on a schedule. This guide walks through each step in SSMS and Transact-SQL, and shows where the setup goes wrong for regulated businesses such as clinics, law firms, banks and mortgage companies.

Securetechie
Keep SQL Server Audits Review-Ready
Securetechie provides proactive IT management and cybersecurity for Southern California businesses handling sensitive data and compliance needs.
Visit

TL;DR

  • SQL Server Audit has three parts: the server audit (where events go), the server audit specification (instance-level events) and the database audit specification (events in one database).
  • A new audit is created disabled. Nothing is recorded until you enable it.
  • Write to a file target, audit only what a regulator or your policy requires, and filter by object and user so the log stays readable.
  • An audit nobody reviews proves nothing. Set retention, protect the files from the people being audited, and schedule a review.
  • If the audit has to stand up to HIPAA, SOC 2 or a bank examiner, the evidence process matters as much as the T-SQL.

Table of Contents

What SQL Server Audit records

SQL Server Audit tracks and logs Database Engine events. According to Microsoft's documentation, a server audit can contain server audit specifications for server-level events and database audit specifications for database-level events, and the results can be written to an audit file or to a Windows event log.

Three objects make up the feature:

ObjectScopeWhat it holds
Server auditThe SQL Server instanceThe destination (file, Security log or Application log), queue delay and failure behavior
Server audit specificationThe instanceServer-level action groups, such as logins and permission changes
Database audit specificationOne databaseDatabase actions such as SELECT, INSERT, UPDATE, DELETE and EXECUTE

You can have several audits on one instance. That is useful when a compliance team wants one stream (logins and permission changes) and a data-owner team wants another (reads of a loan or patient table).

Server-level and database-level events

Server-level events are collected through action groups. Microsoft's action group reference lists groups such as FAILED_LOGIN_GROUP, SUCCESSFUL_LOGIN_GROUP, LOGIN_CHANGE_PASSWORD_GROUP, BACKUP_RESTORE_GROUP, DATABASE_CHANGE_GROUP and AUDIT_CHANGE_GROUP. A group called BATCH_COMPLETED_GROUP applies to SQL Server 2022 and later.

Database-level events are specific actions: SELECT, INSERT, UPDATE, DELETE, EXECUTE, RECEIVE and REFERENCES. They can be limited to a database, a schema or an object such as a table, view or stored procedure, and to specific database principals. That scoping is what keeps an audit useful, and we return to it below.

Decide what to audit before you click anything

The most common failure is not technical. Someone turns on every action group, the log grows by gigabytes a day, and nobody can find the one event that matters. Start with a short written scope.

For most small and mid-size regulated businesses, a workable first scope is:

  1. Authentication: failed and successful logins, so you can see password guessing and unusual access hours.
  2. Privilege changes: changes to logins, roles and permissions, so you can show who gave whom access.
  3. Changes to the audit itself: so nobody can quietly switch auditing off.
  4. Sensitive data access: reads and writes on the specific tables that hold patient records, client files, loan applications or account data.
  5. Schema and backup activity: database changes and backup or restore operations.

The fourth item is where scope discipline matters. Audit the tables that hold regulated data, not the whole database. If you are also reducing exposure with column-level protections, our guide to SQL Server data masking covers the companion control.

Step 1: Create the server audit

The server audit defines where events land. You can create it in SSMS or with T-SQL.

In SQL Server Management Studio

Per Microsoft's walkthrough:

  1. In Object Explorer, expand the Security folder.
  2. Right-click Audits and select New Audit.
  3. Enter an audit name, the queue delay, the on-audit-log-failure option and the audit destination.
  4. Click OK.

The queue delay is the time in milliseconds that can pass before audit actions are forced to be processed. A value of 0 means synchronous delivery. The default minimum is 1000 (one second), and the maximum is 2,147,483,647. Synchronous delivery gives the strongest guarantee that an event is written before the action completes, but it costs performance on busy systems.

With Transact-SQL

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

CREATE SERVER AUDIT accepts FILE, APPLICATION_LOG, SECURITY_LOG, URL and EXTERNAL_MONITOR as target locations, and the URL target is not supported for SQL Server. Pick the file target unless you have a specific reason to use an event log.

Choose the target carefully

  • File: the simplest option and the easiest to read back with a function. Put it on a dedicated volume so a full data drive does not stop the server.
  • Windows Application log: easy to browse in Event Viewer but shared with other applications.
  • Windows Security log: the strongest option for tamper resistance and for forwarding to a monitoring platform, but it needs extra configuration (see the troubleshooting section).

Microsoft warns that SQL Server can generate thousands of audit records per second under heavy load, which can cycle an event log quickly and affect other applications that read from it. Its suggested mitigations are a larger target log (4 GB is not unreasonable for a very detailed specification), fewer audited events, and writing to a file instead of the event logs.

Step 2: Add a server audit specification

The server audit specification lists the instance-level action groups to capture and ties them to the audit.

CREATE SERVER AUDIT SPECIFICATION [SrvSpec_Compliance]
FOR SERVER AUDIT [Audit_Compliance]
    ADD (FAILED_LOGIN_GROUP),
    ADD (SUCCESSFUL_LOGIN_GROUP),
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
    ADD (SERVER_PERMISSION_CHANGE_GROUP),
    ADD (DATABASE_CHANGE_GROUP),
    ADD (BACKUP_RESTORE_GROUP),
    ADD (AUDIT_CHANGE_GROUP)
WITH (STATE = ON);
GO

In SSMS, the same step is done under Security > Server Audit Specifications.

AUDIT_CHANGE_GROUP deserves special attention. It records changes to the audit configuration itself, so an attempt to alter or drop the audit leaves a trace. An audit trail is only as trustworthy as your ability to show it was not edited by the people it watches.

SUCCESSFUL_LOGIN_GROUP can be noisy on servers where applications open many connections. If the volume is too high, keep failed logins and privileged-account activity and drop the rest until you have a reason to add it back.

Step 3: Add a database audit specification

This is the step most guides skip past, and it is where the audit becomes useful for protecting sensitive records. A database audit specification is created inside the database you want to audit and attached to a server audit. It can list audit action groups or individual actions on a securable, such as a schema or table, for particular principals.

USE LoanDB;
GO
CREATE DATABASE AUDIT SPECIFICATION [DbSpec_Borrowers]
FOR SERVER AUDIT [Audit_Compliance]
    ADD (SELECT, INSERT, UPDATE, DELETE
         ON OBJECT::dbo.Borrowers BY public),
    ADD (EXECUTE ON SCHEMA::dbo BY public)
WITH (STATE = ON);
GO

Three habits keep this readable:

  • Name the objects. Audit the borrower, patient, matter or account tables, not the entire database.
  • Think about the principal. Auditing public captures everyone. If application service accounts generate millions of routine reads, consider auditing human users and privileged roles separately from the application.
  • Keep one specification per purpose. A specification for regulated tables and another for schema changes are easier to explain to an examiner than one long list.

As a rule of thumb, audit writes broadly and reads narrowly. Reads on high-traffic tables produce the volume that hurts performance. AWS's guidance on SQL Server audits makes the same point: audit only the databases of interest, filter to relevant users and objects, and balance audit requirements against performance, since non-optimal configurations can create bottlenecks. If your instance is already under strain, review our SQL Server performance tuning guide first.

Step 4: Enable the audit

The audit object is created in a disabled state. Specifications you created with STATE = ON still record nothing until the audit itself is enabled.

Audit specifications begin recording after activation

USE master;
GO
ALTER SERVER AUDIT [Audit_Compliance] WITH (STATE = ON);
GO

In SSMS, right-click the audit under Security > Audits and choose Enable Audit. This is the answer to the most common search about this feature: enabling SQL Server Audit is a separate action from creating it.

Verify the state straight away:

SELECT name, status_desc, audit_file_path
FROM sys.dm_server_audit_status;

You want to see STARTED. Anything else needs attention before you rely on the audit.

Step 5: Read and verify the audit log

Microsoft notes that file targets can be read with the Log File Viewer in SSMS or with the fn_get_audit_file function, and event log targets with Windows Event Viewer. To query files:

SELECT event_time, action_id, succeeded, server_principal_name,
       database_name, object_name, statement
FROM sys.fn_get_audit_file('D:\\SQLAudit\\*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;

In SSMS, right-click the audit and choose View Audit Logs.

To prove the audit works, run a test: attempt a login with a wrong password, change a role membership in a test database, and select from an audited table. Then confirm each shows up. Three views help you see the state of the feature: sys.dm_audit_actions, sys.dm_server_audit_status and sys.dm_audit_class_type_map.

Retention, protection and review

Enabling the audit is the easy part. Three operational questions decide whether it holds up later.

How long do you keep it? Microsoft says logs must be reviewed and archived periodically so the target has enough space. How long to keep them is a policy and regulatory decision. Our article on log retention policy explains how to set it, and our piece on SOC 2 logging requirements shows how U.S. teams evidence it. Copy audit files to protected, backed-up storage, and size your backup scheme with our backup retention calculator.

Who can change or delete it? If the database administrator can delete the files, an examiner has reason to question them. Store audit files where the SQL Server service account can write but administrators of the database cannot modify or remove them, or forward events to a separate monitoring system. Keep AUDIT_CHANGE_GROUP on so any alteration is itself recorded.

Who reads it, and when? SQL Server collects events but gives you no built-in way to alert on them. A weekly review of failed logins, permission changes and access to the regulated tables is a sensible minimum. Assign an owner and keep a record that the review took place. That record is often the first thing an auditor asks for.

Retention, protection and review

Troubleshooting: why events are not captured

Most silent failures come from the Security log target. Microsoft lists these causes:

  • The audit object access setting is not configured in Windows.
  • The SQL Server service account lacks the Generate security audits permission.
  • The account lacks permission to the registry key for the Security event log.
  • A domain policy overwrote the local settings.
  • The Security log is full and not set to overwrite old events. Windows then logs event 1104 and records no further security events.

If the audit enters a failed state, the Dedicated Administrator Connection can still perform audited events, per Microsoft. Choose the ON_FAILURE setting deliberately. CONTINUE keeps the server available but can leave a gap in the record. SHUTDOWN stops the instance if it cannot write, which protects the record but affects availability. For a bank or lender, that decision belongs in writing with the compliance owner.

What auditors want to see

A clean audit configuration is not the same as audit evidence. Healthcare practices, law firms, lenders and banks are usually asked to show that logging exists, that it is protected, that it is retained and that someone reviews it. Specific controls differ by framework (HIPAA, SOC 2, SEC-related expectations, CMMC), so map your audit scope to the framework that applies to you. Our guide to IT audit preparation for SMBs lists the documents that typically get requested.

Our perspective

Most small businesses we speak with do not have a SQL Server problem. They have an ownership problem. The audit gets switched on during a project, nobody is assigned to read it, and the files sit on the same server as the data until a disk fills up.

We build IT around prevention, not break-fix. For auditing, that means a written scope, a protected destination, monitoring that runs around the clock and a named reviewer, so the log works as evidence and as an early warning.

Security and compliance come first in how we work, across HIPAA, GDPR, SOC 2 and CMMC. An audit configuration should be designed with that framework in mind from the start, not retrofitted the week before an examiner arrives.

 

Alex

Getting the audit to hold up

If your SQL Server holds patient records, client matters, loan files or account data, the configuration above is the technical half. The other half is retention, protected storage, monitoring and a review you can document. Secure Techies provides managed IT services with 24/7 monitoring, plus compliance and security audits covering HIPAA, GDPR, SEC, SOC 2 and CMMC for small and mid-size businesses in Southern California.

Securetechie

For the backup side of your audit evidence, see our backup and disaster recovery service.

FAQ

How do I enable SQL Server Audit?

Create a server audit under Security > Audits in SSMS, or with CREATE SERVER AUDIT. Then add a server audit specification, a database audit specification or both. Finally, enable the audit itself, because it is created disabled and records nothing until you do.

How do I view the SQL Server audit log?

For a file target, use the Log File Viewer in SSMS or query the files with the fn_get_audit_file function. For the Windows Security or Application log targets, use Event Viewer. Check sys.dm_server_audit_status to confirm the audit is running.

Does SQL Server Audit slow down the server?

It can. Heavy auditing generates many records per second, so audit only the databases, objects and users you need, and prefer a file target over an event log. Test any new specification during a normal business day and watch the volume.

What is the difference between a server audit specification and a database audit specification?

A server audit specification captures instance-level events such as logins, role changes and backups. A database audit specification captures actions in one database, such as SELECT or UPDATE on a table. Both write to the same server audit.

Sources