Microsoft Practice
Enable SQL Server Audit. Then actually read it.
The audit objects take an hour to configure. The control is the weekly review, and the payoff doubles as licensing evidence.
The audit objects take an hour to configure. The control is the weekly review, and the payoff doubles as licensing evidence.
How to configure SQL Server Audit properly, run a weekly review that catches what matters, and turn the same log into licensing defense evidence.
SQL Server Audit is the engine native framework that records server and database events to a binary log file, the Windows Security log, or the Application log. The official feature documentation defines its three objects: the server audit, the server audit specification, and the database audit specification.
It captures what you tell it to capture through action groups: logins, permission changes, schema modification, data access, backup and restore events. Since SQL Server 2016 Service Pack 1 the full feature works in every edition, so Standard edition estates have no excuse left.
File targeted audits with sensible action groups run at low single digit percent overhead in our testing experience. The expensive mistake is auditing broad data access patterns on busy OLTP databases; scope to the sensitive objects instead.
Create the server audit with a file target, define the failure behavior, then bind one server specification and per database specifications scoped to what compliance actually requires. The step by step creation guide covers both the Management Studio path and the script path.
Minimum viable audit configuration for a regulated estate
| Layer | Setting | Why |
|---|---|---|
| Target | Binary file on dedicated volume | Fast, tamper evident, survives log clears |
| Failure action | Fail operation for regulated databases | Events cannot silently vanish |
| Server spec | Logins, role changes, audit changes | Catches privilege escalation and tampering |
| Database spec | Schema changes, permission grants, sensitive object access | The events regulators actually ask about |
| Retention | Rollover files, 12 month minimum | Covers the audit and the true up window |
| Review | Weekly scripted pass plus alerting | An unread log is a liability, not a control |
The binary files are read with the audit file function documented in the audit records reference. Wrap it in a scheduled job that filters for the dozen event types you care about, writes exceptions to a review table, and alerts on the genuinely rare ones: audit state changes, new sysadmin members, permission grants on sensitive schemas.
The weekly pass looks for privilege drift, schema drift, and audit tampering. Everything else is noise until an incident makes it signal, which is why retention matters more than realtime dashboards.
The standard advice treats SQL Server Audit purely as a security control and stops at enabling it for the compliance checkbox. We disagree. In roughly 30 of the 30 to 40 estates Morten Andersen reviewed in 2024 to 2025, the same audit trail was the decisive licensing evidence: it documented when instances were decommissioned, when features stopped being used, and which logins were service accounts versus humans. Estates with 12 months of audit history defended Microsoft true ups and audits from records; estates without it negotiated from recollection. The buyer side move is one control serving two masters: configure the audit for security, retain it for licensing defense.
Source: Redress Compliance advisory engagement file, 2024 to 2025.
An enabled audit nobody reads is a liability with a green checkbox. The control is the review cadence, not the specification.
Audit history is contemporaneous evidence of deployment reality. In Microsoft true ups and audits, login patterns separate human users from service accounts, event timestamps prove decommission dates, and the absence of activity supports unused instance claims, all against Product Terms definitions that reward precision.
Keep the rollover files for at least 12 months, archive them with the same discipline as financial records, and export a quarterly summary into your license position file. It costs almost nothing and pays exactly when the stakes are highest.
The Microsoft practice covers SQL Server licensing defense, and the Microsoft hub holds the related true up and hybrid licensing guides. For estate wide leakage, run the software spend health check.
Create a server audit object with a file target and deliberate failure behavior, bind a server audit specification for instance events, then add database audit specifications scoped to sensitive schemas. Management Studio and script paths both work.
Minimally when scoped: file targeted audits with login, role, and schema action groups run at low single digit overhead. Auditing broad data access on busy OLTP databases is where cost appears; scope to sensitive objects instead.
Privilege drift, schema drift, and tampering: new role members, permission grants outside change windows, unexplained object changes, audit state changes, and log gaps. Script the pass and alert on the rare events.
Yes. Since SQL Server 2016 Service Pack 1, the full audit feature including database audit specifications works in all editions, not just Enterprise.
Decisively. Login patterns separate humans from service accounts, timestamps prove decommission dates, and inactivity supports unused instance claims. Estates with 12 months of history defended true ups from records, not recollection.
Audit configuration baseline, weekly review script outline, retention policy template, and the true up evidence checklist.
Used across more than five hundred enterprise engagements. Independent. Buyer side. Built for procurement leaders running the next renewal cycle.
