master Server Audits

Overview

An audit on SQL Server is three objects that have to line up.

Object What it says
The audit Where the records go, and what to do if they cannot be written.
A server audit specification Which server level actions to record.
A database audit specification, in each database The same for that database.

Any of the three can be present and disabled, and a server audit with no specification pointed at it is a configuration that records nothing while looking, in every management dialog, exactly like one that does.

Where to find it

Expand a server in the tree, expand the master database, then Master → Server Audits.

Shown on SQL Server 2008 and newer.

One setting deserves the top severity and gets it

ON_FAILURE = SHUTDOWN means the instance stops when the audit cannot write, which turns a full disk on the audit volume into an outage.

It is sometimes the right answer, and it is never an accident worth leaving undiscovered.

How the database specifications are gathered

By walking the databases, because there is no server wide view of them.

The walk skips databases that are offline, restoring or not readable by this login, and the statement is assembled from the list first rather than written out, because a database that cannot be reached would otherwise fail the batch and take the server half of the page with it.

The caveat line names any database that was skipped.

Reading the grid

Column What it is
Audit The audit name.
Writes to File, the Windows application log, or the Windows security log, with the path for a file audit.
State Started or stopped, and whether it is enabled.
On failure Continue, fail the operation, or shut down the instance.
Specifications Which specifications point at this audit, server and database. None is the finding.
Records What the specifications record, summarized.
Current file The audit file being written now, and its size.

The toolbar

Control What it does
Security posture Opens the Security Posture report.
Server permissions Opens master Server Permissions.
Error log Opens the Error Log report, where an audit failure is recorded.

Where the data comes from

Source What it gives
sys.server_audits The audits, their type, state and on_failure.
sys.server_file_audits The file path, maximum size and rollover settings.
sys.server_audit_specifications, sys.server_audit_specification_details The server level specifications and what they record.
sys.database_audit_specifications in each database The database level specifications, gathered by walking the databases.
sys.dm_server_audit_status Whether each audit is actually running, and the current file.
Report Why you would go there
Security Posture Where auditing belongs in the instance level picture.
master Server Permissions The grants an audit would be recording changes to.
Error Log What SQL Server said when an audit could not write.
Disk Space The volume an audit with ON_FAILURE = SHUTDOWN depends on.
master Change Ledger The change record that exists whether or not auditing is on.

Frequently asked questions

We have an audit and it records nothing. Look at the Specifications column. An audit with no specification pointed at it is a destination with nothing sending to it, and every dialog shows it as configured.

ON_FAILURE is SHUTDOWN. Should I change it? That depends on your compliance requirement, and the page does not decide it for you. What it will say is that the instance stops if the audit volume fills, which is a fact whoever owns that volume should know.

A database is missing from the specification list. It was skipped because it is offline, restoring, or not readable by this login. The caveat line names it.

Where do the audit records go? Whatever the Writes to column says. For a file audit the path is there, and sys.fn_get_audit_file is how you read it.