How to enable SQL Server Audit and review the log. The weekly review is the control that matters.

Configuring SQL Server Audit takes about an hour. The value comes from a weekly review of the log and from keeping a year of history, which also answers Microsoft's licensing questions.

What does SQL Server Audit actually capture?

SQL Server Audit records the server and database events you select and writes them to a binary audit file, the Windows Security log or the Windows Application log. It is built into the Database Engine, so there is nothing extra to install or license.

What lands in the log is decided by action groups. The ones that matter for most organizations cover logins, permission changes, schema modification, data access, and backup and restore events. The official feature documentation describes the full model.

The three objects, in the order you create them

1. Server audit. The destination: where events are written, how files roll over, and what SQL Server does if it cannot write. It is created disabled and records nothing until you switch it on.
2. Server audit specification. Instance level action groups such as failed logins and server role changes. Each server audit can have one server audit specification.
3. Database audit specification. Action groups for one database, which can be narrowed to specific schemas, objects and principals. Each database can have one per server audit.

Which editions support it?

Server level auditing works in every edition. Database audit specifications were limited to Enterprise, Developer and Evaluation until SQL Server 2016 Service Pack 1, and since then the full feature runs in every edition. A Standard edition shop has no technical reason left to go without it.

What it costs at runtime

In our testing, file targeted audits with sensible action groups add overhead in the low single digit percent range. The cost shows up when teams audit broad data access on busy OLTP databases, where every SELECT against a hot table becomes an audit record.

Two settings help. QUEUE_DELAY defaults to 1,000 milliseconds, which allows SQL Server to batch writes; setting it to 0 forces synchronous delivery and adds latency to every audited statement. A WHERE predicate on the server audit filters events before they are written, for example to drop a monitoring login that connects every few seconds.

How do you enable a server audit correctly?

Create the server audit with a file target and a chosen failure behavior, then bind one server audit specification and a database audit specification for each database that holds regulated data. Enable the audit and each specification last. Microsoft's step by step creation guide covers both the Management Studio path and the Transact SQL path.

Which action groups belong in each specification?

• Server specification. FAILED_LOGIN_GROUP, SUCCESSFUL_LOGIN_GROUP, SERVER_ROLE_MEMBER_CHANGE_GROUP, SERVER_PERMISSION_CHANGE_GROUP, AUDIT_CHANGE_GROUP and BACKUP_RESTORE_GROUP.
• Database specification. DATABASE_ROLE_MEMBER_CHANGE_GROUP, DATABASE_PERMISSION_CHANGE_GROUP, SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP and SCHEMA_OBJECT_CHANGE_GROUP.
• Sensitive tables only. SELECT, INSERT, UPDATE and DELETE actions on the named tables or schemas that hold card, health or payroll data, scoped to the public role so every user is covered.

Microsoft flags DATABASE_OBJECT_CHANGE_GROUP and DATABASE_OBJECT_ACCESS_GROUP as groups that can produce large volumes of records. Add them only with a clear reason and a volume test.

The settings that cause trouble later

• ON_FAILURE. CONTINUE is the default and leaves gaps when the target fills or goes offline. FAIL_OPERATION blocks the audited actions until writing resumes, and SHUTDOWN stops the instance. Pick per database according to how much downtime you can accept, and write the decision down.
• Audit the audit. Turning an audit on or off is always recorded. Creating, changing or dropping a specification is recorded only if AUDIT_CHANGE_GROUP is in the specification, so include it.
• File security. Give the audit folder its own permissions. A DBA or server administrator who can delete the files defeats the control.
• Security log target. Writing to the Windows Security log needs the service account in the Generate security audits policy and Audit object access enabled for success and failure. Most teams find the file target easier to protect and to read.

Reading the log without drowning in it

The binary files are read with sys.fn_get_audit_file, and the fields it returns are listed in the audit records reference. Event times come back in UTC. From SQL Server 2017 the output also carries client_ip and application_name, which make the licensing work below much easier.

Wrap the function in a SQL Server Agent job that filters for the dozen or so event types you care about and writes exceptions to a review table. Alert immediately on the rare ones: audit state changes, new sysadmin members and permission grants on sensitive schemas.

What should the weekly SQL Server audit review look for?

The weekly pass looks for three things: privilege drift, schema drift and signs of tampering. Everything else is noise until an incident turns it into evidence, which is why retention matters more than a real time dashboard.

• Privilege drift. New server role members, permission grants made outside change windows, and high privilege accounts whose owner has left.
• Schema drift. Object changes on production databases that no change record explains.
• Tampering signals. Specification changes, audit stops, and gaps in the file sequence or in event_time.

How the review cadence builds up over a year

How to check your own coverage

You can confirm what is configured without opening a single audit file. These catalog views and functions exist on every supported version:

• sys.dm_server_audit_status. Shows whether each audit is started, and the current file name and size.
• sys.server_audits and sys.server_file_audits. Show the ON_FAILURE setting, QUEUE_DELAY, file path, MAXSIZE and rollover limits.
• sys.server_audit_specification_details and sys.database_audit_specification_details. List the action groups each specification actually contains.
• sys.dm_audit_actions. Translates the four character action_id codes in the log into readable names for the review script.

Run the same checks across every production instance. A single instance with the audit stopped or without a database specification is the gap an investigator finds first, and it is also the instance for which you will have no licensing history.

How much storage does a year of audit files need?

Size the target from measured volume. Run the audit for two weeks, read the file sizes from sys.dm_server_audit_status, and project forward. The example below is hypothetical and shows the arithmetic.

Keeping only a short window on the server limits the damage if the volume fills, while the archive holds the full year. Set MAX_ROLLOVER_FILES deliberately: the default is unlimited, and SQL Server deletes the oldest file only once a limit is reached.

How the setup changes with the size of the environment

With five to ten instances, the weekly script can run on each server and write to a local review table. The control holds up as long as someone outside the DBA team signs off each week and the sign off is kept.

With several hundred instances, deploy the audit objects from one Transact SQL script held in source control so every server matches. Copy closed .sqlaudit files to a central share, where one server reads them with sys.fn_get_audit_file and loads a single review table. The coverage checks above then run as one report across the whole fleet.

What have we seen in SQL Server audit reviews in 2024 and 2025?

Across roughly 30 to 40 Microsoft customer environments I reviewed in 2024 and 2025, SQL Server Audit was either off or switched on and never read. Three patterns came up again and again.

• Enabled, then ignored. Most regulated organizations had audit specifications in place, but fewer than 1 in 4 had anyone reviewing the output on a schedule.
• Failed logins mistaken for an audit. The default login auditing setting, which writes failed logins to the error log, was treated as auditing. Schema changes and permission grants went unrecorded.
• Licensing value missed. Teams that kept their audit logs answered Microsoft true up questions far better than teams that had discarded them.

Why treating the audit as a pure security control wastes half its value

The usual advice treats SQL Server Audit as a security control and stops once it is enabled for the compliance checklist. We disagree. In roughly 30 of those 30 to 40 environments, the same audit trail was the deciding licensing evidence.

It documented when instances were decommissioned, when features stopped being used, and which logins were service accounts and which were people. Customers with 12 months of audit history answered Microsoft true ups and audits from records, while those without it negotiated from memory. Configure the audit for security and retain it for licensing.

An enabled audit that no one reads is a liability with a green checkbox. The control is the review cadence, and the specification only makes it possible.

How does the SQL Server audit log help in Microsoft licensing reviews?

Audit history is contemporaneous evidence of how SQL Server was actually deployed and used. In Microsoft true ups and audits it separates people from service accounts, proves decommission dates with timestamps, and shows the absence of activity that supports an unused instance claim.

The Product Terms define licensing by what ran and who accessed it, so a dated record carries more weight than a recollection.

Where the evidence counts, and where it does not

• Server and CAL instances. Login records help you count the people and devices that need CALs. They do not remove anyone: under Microsoft's multiplexing rules, users behind a pooled service account still need CALs, so use the log to count correctly.
• Per core instances. Cores are counted regardless of how many people connect, so the login split changes nothing here. The value is in decommission dates. An installed instance that kept running still needs licenses even if no one logged in, so pair the idle record with the audit stop event or the date the service was removed.
• Developer edition claims. Developer edition may not serve production. Records showing that only named developers and test tools connected, by login and application_name, back up the claim.

What the auditor will say, and what to answer

• "The instance appears in the inventory scan, so it needs licenses for the whole period." Show the last successful login and the final server state change from the audit history, and ask for the count to stop at the decommission date.
• "Every login in the database is a user who needs a CAL." Provide the quarterly split of named people and service accounts, with the people behind each service account counted once.
• "This Developer edition server looks like production." Produce the login and application history for the review period. If production traffic does appear, you find it before the auditor does and can add it at your next true up on your agreement pricing.
• "Without records, we will assume peak usage." Hand over the quarterly summaries and the archived files behind them. Records usually settle this point quickly.

Common mistakes that cost money later

• Letting rollover delete history. A small MAX_ROLLOVER_FILES value with no archive leaves you with weeks of evidence when Microsoft asks about the year.
• Decommissioning without a record. Stopping an instance with the audit already off leaves no proof of the date. Keep the audit running until shutdown and archive the final files.
• Keeping raw files only. Few teams can query a year of binary files under audit deadline pressure. The quarterly summary is what you actually hand over.

Keep at least a year of rollover files, archive them with the same discipline as financial records, and export a quarterly summary into your license position file. The storage costs almost nothing, and the records pay off in a true up or an audit, when the amounts at stake are largest.

Our SQL Server audit defense service and the EA true up guide show how that evidence is used.

What to do next

1. This month. Create a file targeted server audit with a deliberate ON_FAILURE choice on every production instance.
2. Same change. Bind a server audit specification covering logins, role changes and audit changes.
3. Database by database. Scope database audit specifications to sensitive schemas and tables, not whole databases.
4. Within a week. Schedule the weekly scripted review with alerts on tampering signals.
5. Retention. Set rollover to hold a local window, archive every closed file for the full retention period, and treat the archive like financial records.
6. Every quarter. Export a summary into your Microsoft license position file.

Our Microsoft practice covers SQL Server licensing defense, and the Microsoft hub holds the related true up and hybrid licensing guides, including SQL Server licensing in 2026. To find overspend across all your vendors, run the software spend health check.

Frequently asked questions