Auditing SQL Server: Logins, Database Create/Drop, and Metadata Changes

Scope: What This Covers, and What It Doesn't

Three categories, in SQL Server's own vocabulary:

• Login events — successful and failed authentication attempts.
• Database create/drop — CREATE DATABASE, ALTER DATABASE, DROP DATABASE, server-scoped operations.
• Metadata changes — schema-level DDL: CREATE/ALTER/DROP on tables, views, procedures, permissions. Changes to sys.objects/sys.columns, not to the rows inside those tables.

Deliberately out of scope: row-level DML auditing (who changed this specific value in this specific row). That's a heavier, different problem, solved by temporal tables, Change Data Capture, or Audit's SELECT/INSERT/UPDATE/DELETE action groups, worth its own article rather than a bullet point in this one.

Method 1: AuditLevel and the Error Log (the Baseline Everyone Already Has)

SSMS's Server Properties → Security → "Login auditing" setting is really just a registry value, AuditLevel, read by SQL Server at startup: 0 = none, 1 = successful only, 2 = failed only (the default), 3 = both. Whatever it's set to, matching events land as plain text in the SQL Server error log, message 18453 (successful, non-trusted), 18454 (successful, trusted), 18456 (failed).

This costs nothing to turn on and is already partially on by default, which is exactly why it's the baseline, not the answer. It has no coverage for database create/drop or metadata changes at all, it's a text log subject to rotation, and it's not tamper-evident: anyone with access to the file (or a sysadmin session) can edit or delete it with nothing to say it happened.

Method 2: SQL Server Audit

The feature actually built for this, since SQL Server 2008, and in Standard Edition (not just Enterprise) since SQL Server 2016 SP1. Three pieces: a Server Audit (the destination, a binary .sqlaudit file, the Windows Application log, or the Windows Security log), a Server Audit Specification (server-scoped events, logins and CREATE/ALTER/DROP DATABASE among them), and optionally a Database Audit Specification per database (schema-level DDL, permission changes, scoped to that database).

-- 1. Destination
CREATE SERVER AUDIT AG_Compliance_Audit
TO FILE (FILEPATH = 'D:\Audit\', MAXSIZE = 500 MB, MAX_ROLLOVER_FILES = 20)
WITH (ON_FAILURE = FAIL_OPERATION, QUEUE_DELAY = 1000);

ALTER SERVER AUDIT AG_Compliance_Audit WITH (STATE = ON);

-- 2. Server-scoped events: logins, database create/drop, and who touches the audit itself
CREATE SERVER AUDIT SPECIFICATION AG_Server_Audit_Spec
FOR SERVER AUDIT AG_Compliance_Audit
    ADD (FAILED_LOGIN_GROUP),
    ADD (SUCCESSFUL_LOGIN_GROUP),
    ADD (DATABASE_CHANGE_GROUP),
    ADD (AUDIT_CHANGE_GROUP)
WITH (STATE = ON);

-- 3. Database-scoped: schema/metadata changes, per database
CREATE DATABASE AUDIT SPECIFICATION AG_Db_Audit_Spec
FOR SERVER AUDIT AG_Compliance_Audit
    ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);

Query the result with sys.fn_get_audit_file, or read it through SSMS's Log File Viewer:

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

AUDIT_CHANGE_GROUP above is not decoration: it's what answers the auditor's next question, who's watching the watcher, by recording changes to the audit configuration itself.

Writing to the Windows Security log instead of a file needs two things most people forget on the first attempt: Object Access auditing enabled via local/group security policy, and the Generate security audits user right granted to the SQL Server service account. Skip either and the audit silently produces nothing there, with no error on the SQL Server side to say so.

Method 3: DDL Triggers and Logon Triggers

The pre-2008 approach, still fully current: a trigger that fires on the DDL event itself, inline with the transaction, with the DDL captured as XML via EVENTDATA(). Database-scoped DDL uses DDL_DATABASE_LEVEL_EVENTS; CREATE/ALTER/DROP DATABASE, being server-scoped, needs ON ALL SERVER FOR DDL_SERVER_LEVEL_EVENTS instead. Logins get their own separate mechanism, a logon trigger:

CREATE TRIGGER trg_AuditLogon
ON ALL SERVER FOR LOGON
AS
BEGIN
    INSERT INTO master.dbo.LoginAuditLog (LoginName, LoginTime, ClientHost)
    SELECT ORIGINAL_LOGIN(), GETDATE(), HOST_NAME();
END;

The one thing neither SQL Server Audit nor Extended Events can do: a logon trigger can ROLLBACK and actually reject the connection, and a DDL trigger can roll back the DDL statement it just watched happen. That makes triggers the only method on this list that's preventive rather than purely observational.

Triggers also aren't independent oversight by default: anyone with enough permission to alter the trigger or the logging table can quietly remove their own trail, unless those objects are locked down with permissions separate from the DBA's own. SQL Server Audit's configuration objects have the same theoretical exposure, but Audit at least has AUDIT_CHANGE_GROUP to catch it happening; a hand-rolled trigger and log table have whatever protection you built yourself, and no more.

Method 4: Targeted Extended Events

Instead of one of Audit's broad action groups, a custom Extended Events session can subscribe to exactly the events wanted: sqlserver.login for successful connections, error_reported filtered to error number 18456 for failures, database_created/database_dropped for database-level DDL, and object_created/object_altered/object_deleted for schema-level metadata changes (filtered to ddl_phase = 1, since each fires twice per statement: once on start, once on commit or rollback).

This is the lowest-overhead structured option and works on every edition back to 2008, but it comes with none of Audit's packaging: no fn_get_audit_file equivalent (read the event file target's XML yourself), no separate audit-configuration permission model, and a session that silently stops existing after a restart unless STARTUP_STATE = ON was set when it was created, a common, quiet gap. One asymmetry worth knowing before relying on it: database_created/database_dropped cannot be scoped to a specific database name by a session-level predicate, at the moment either fires, neither the collected connection context nor the event's own field resolves to the database being created or dropped, so these two are always instance-wide; object_created/object_altered/object_deleted scope to specific databases correctly.

Comparison

sqmSQLTool: What's Covered, What Isn't

Worth being precise here, because the word "audit" already means two different things in this module:

Practical Guidance

• Compliance-grade requirement, with retention and evidence an auditor will actually ask for: SQL Server Audit. Decide the ON_FAILURE tradeoff deliberately, and enable AUDIT_CHANGE_GROUP so the audit configuration itself is covered.
• Need to actively prevent an action, not just record it, a decommissioned login that must never connect, a table that must never be dropped outside a change window: a logon or DDL trigger, alongside Audit, not instead of it. Test its failure path before production.
• Stuck on an edition or version without SQL Server Audit, or want the lowest possible overhead for a narrow set of events: targeted Extended Events, with STARTUP_STATE = ON set from the start.
• Never treat AuditLevel plus the error log as sufficient evidence on its own. It's a reasonable first line of visibility and exactly what most instances already have; it was never designed to be tamper-evident or complete.

Questions People Actually Ask