Contents
The auditor asks: "Who changed what in this database, and what is your evidence?" The team lists five features: Audit is on, CDC is there, we use temporal tables, an Extended Events session is running, and ledger came with the upgrade. All true, and each answers a different question. This article tests these five SQL Server features against the same five questions: what each records, what it does not, and which one stands on its own as evidence.
The Questions a Trail Must Answer
Splitting the audit question into five parts makes the choice easier:
- Schema: who created, altered or dropped a table, view or procedure?
- Data: who inserted, updated or deleted rows?
- Values: what was the value before and after the change?
- Reads: who read the sensitive table?
- Integrity: if the trail itself is changed later, would anyone notice?
SQL Server Audit
SQL Server Audit is the feature designed for compliance and audit. It records server level events (logins, role membership, permission changes) and database level events (schema changes, SELECT, INSERT, UPDATE, DELETE on chosen tables). Each record holds the time, the login, the session number, the object and the text of the statement that ran. Records are written outside the database, to a file or to the Windows event log. Database level auditing has been in every edition since SQL Server 2016 SP1. A ready setup script that records schema changes and deletes is in the who dropped that table article.
What it does not record: the changed values. The text of an UPDATE is written, but not which row moved from which value to which. On integrity: stopping and restarting the audit is written to the audit itself as a session change event, so the gap is visible. But anyone who can write to the folder holding the files can delete a file; folder permissions and moving the files into your SIEM are therefore part of the control.
Extended Events
Extended Events is SQL Server's general purpose event infrastructure; SQL Server Audit itself is built on it. You can define a session that captures completed statements, the login and the application name, and it is very valuable for investigating performance problems. But it is designed for diagnostics: a privileged user can stop the session quietly, the target files are ordinary files, and the default retention setting allows events to be lost. It can be used for auditing, but where SQL Server Audit is available it does not replace it as evidence.
Change Data Capture and Change Tracking
Change Data Capture (CDC) reads the transaction log and writes the before and after values of changed rows into separate change tables. It is designed for incremental loads into a data warehouse and does that job well. It needs SQL Server Agent and has been in Standard edition since SQL Server 2016 SP1. What it does not record: who made the change. CDC tables have no user column. The default retention is three days; the cleanup job deletes older records.
Change Tracking is lighter still: it keeps only which row changed and a version number. Neither values nor users. It is meant for application synchronisation, not for audit.
Temporal Tables
System versioned temporal tables (SQL Server 2016 and later) keep the old version of every row in a history table together with its validity period. They answer "what was this customer's address on 3 March" directly with FOR SYSTEM_TIME AS OF. What they do not record: the person who made the change. If you need the user, add a separate column and fill it from the application or a trigger. On integrity: someone with ALTER permission on the table can switch versioning off (SYSTEM_VERSIONING = OFF), edit the history table and switch it back on. A temporal table is a history, not tamper evidence.
Ledger Tables
Ledger tables, introduced with SQL Server 2022, are designed so that tampering is noticed. There are two kinds. In an append-only ledger table the database engine refuses UPDATE and DELETE. In an updatable ledger table changes are allowed, but the old version of every row is kept in a history table. In both kinds every transaction is chained cryptographically, and the sys.database_ledger_transactions view shows the time of the transaction and the database user who made it (principal_name).
Noticing tampering depends on one condition: the database digest (sys.sp_generate_database_ledger_digest) must be taken regularly and kept somewhere the database administrator cannot change. Verification runs against those digests with sys.sp_verify_database_ledger. In Azure SQL the digests can be written to immutable storage automatically; on SQL Server on premises you set that up yourself. A ledger table can be dropped, but the dropped table is renamed and kept, and the drop shows in the ledger. Ledger covers only tables created as ledger tables; an existing table cannot be converted later, it is moved into a new ledger table.
One Table Comparison
| Schema, who | Data, who | Before and after | Who read | Tampering noticed | |
|---|---|---|---|---|---|
| SQL Server Audit | Yes | Yes (statement text) | No | Yes | Partly: a stop is visible, file deletion depends on folder rights |
| Extended Events | Yes | Yes | No | Yes | No |
| Change Data Capture | No | No, no user | Yes | No | No |
| Change Tracking | No | No | No, only which row | No | No |
| Temporal table | No | No, unless you add a column | Yes | No | No |
| Ledger table | Only the ledger table's own operations | Yes (database user) | Yes, in the updatable kind | No | Yes, if the digest is kept outside |
Read down the table and the point is simple: no feature answers all five questions. Read across and the more important point appears: the "who" and "values" questions sit in different tools and can only be joined through time and session information.
How They Work Together
In an enterprise setup the typical combination is this: SQL Server Audit records schema changes, permission changes, and reads and writes on sensitive tables, and the files flow into the SIEM. Tables that need a value history are designed as temporal or ledger tables. If ledger is used, the database digest is taken regularly and written to immutable storage. These three answer "what happened".
The open question is "why, and with whose approval"; that never sits inside the database. SQL Change Guard answers it only for work that goes through it, and it does not set up or manage the features above on target servers. The product keeps its own record in a SQL Server append-only ledger table; where a signing key and an external digest folder are configured, a signed digest of that ledger is written outside the database every hour. For each execution the product records the session number the target server reported and the time; those two make it possible to match the record with the SQL Server Audit entries on the target (session_id and event_time). The "Expected Access Principals" report lists, for each server, the SQL account the product uses and the machines it connected from; a session that does not match this list did not come through the product, which can be turned into a SIEM rule.
Frequently Asked Questions
Does SQL Server Audit affect performance?
By default it buffers records for up to one second and writes them asynchronously (QUEUE_DELAY = 1000), so the effect is small for schema and permission events. Recording every SELECT on a very busy table, on the other hand, produces serious volume; limit the scope to sensitive tables and measure in a test environment before switching it on.
What is the equivalent in PostgreSQL and Oracle?
PostgreSQL has no audit feature in core; the common solution is the pgAudit extension, which writes to the server log. Oracle has Unified Auditing, where audit policies record schema, data and read events. The logic of the questions is the same: who, what, the old value and the integrity of the trail have to be asked separately.
If we move to ledger, do we still need Audit?
Yes. Ledger shows changes in tables created as ledger tables and that those changes were not tampered with. It does not record other tables, reads, logins or permission changes. The two answer different questions.
Who should be able to read the audit files?
Read access belongs with the audit and security team; write and delete rights only with the SQL Server service account. In a setup where the database administrator can delete that folder, the audit is in the hands of the person being audited.
See the record of the "why"
We will show how a request's justification, approval and execution session sit in one record and how they match the audit trail on the target.
Book a Demo →