Introduction
Security isn’t just about encryption—it’s also about accountability. If you need to track who’s accessing your SQL Server, making changes, or altering security settings, SQL Server Audit is your go-to solution.
SQL Server Audit allows you to log and monitor specific database activities, helping organizations comply with security regulations like HIPAA, GDPR, and SOX. In this post, we’ll explore what SQL Server Audit is, why it’s important, and how to set it up.
What is SQL Server Audit?
SQL Server Audit records events happening in your database and logs them in:
✔️ The Windows Security Log
✔️ The Windows Application Log
✔️ A File on Disk
You can track activities such as:
🔹 Login attempts (successful & failed)
🔹 Schema changes (table modifications, permission changes, etc.)
🔹 Data modifications (INSERT, UPDATE, DELETE operations)
🔹 High-privilege actions (GRANT, REVOKE, DENY permissions)
Why Use SQL Server Audit?
✅ Compliance & Regulations – Helps organizations meet regulatory requirements (HIPAA, GDPR, SOX, etc.).
✅ Security & Forensics – Investigate suspicious activity and potential breaches.
✅ Accountability – Track who did what and when within the database.
✅ Real-Time Monitoring – Detect unauthorized changes and failed login attempts.
How to Set Up SQL Server Audit
1️⃣ Create a Server Audit
First, create a server-level audit that defines where audit logs will be stored.
USE master;
GO
CREATE SERVER AUDIT ServerAuditLog
TO FILE ( FILEPATH = 'C:\SQLAudit\' );
GO
2️⃣ Create a Database Audit Specification
Now, set up what events you want to track in a specific database.
USE YourDatabaseName;
GO
CREATE DATABASE AUDIT SPECIFICATION DatabaseAuditSpec
FOR SERVER AUDIT ServerAuditLog
ADD (SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo BY public);
GO
This tracks all SELECT, INSERT, UPDATE, and DELETE operations on dbo schema by any user.
3️⃣ Enable the Audit
Activate both the server audit and database audit specification:
ALTER SERVER AUDIT ServerAuditLog WITH (STATE = ON);
GO
ALTER DATABASE AUDIT SPECIFICATION DatabaseAuditSpec WITH (STATE = ON);
GO
How to View SQL Server Audit Logs
Once auditing is enabled, you can view the logs using this query:
SELECT event_time, succeeded, session_server_principal_name,
database_name, schema_name, object_name, statement
FROM sys.fn_get_audit_file ('C:\SQLAudit\*', DEFAULT, DEFAULT);
GO
This will return:
✔️ Event timestamp
✔️ Success or failure status
✔️ User who performed the action
✔️ Database, schema, and object affected
✔️ SQL statement executed
Managing & Disabling SQL Server Audit
Disabling a Database Audit Specification
To turn off auditing for a database:
ALTER DATABASE AUDIT SPECIFICATION DatabaseAuditSpec WITH (STATE = OFF);
GO
DROP DATABASE AUDIT SPECIFICATION DatabaseAuditSpec;
GO
Removing a Server Audit
If you no longer need auditing, disable and drop the server audit:
ALTER SERVER AUDIT ServerAuditLog WITH (STATE = OFF);
GO
DROP SERVER AUDIT ServerAuditLog;
GO
Best Practices for SQL Server Audit
✔️ Use filters to reduce log size – Track only critical events.
✔️ Secure the audit logs – Restrict access to prevent tampering.
✔️ Monitor regularly – Set up alerts for suspicious activity.
✔️ Backup audit logs – Retain logs for compliance and forensic analysis.
Conclusion
SQL Server Audit is a powerful tool that ensures accountability and helps organizations meet compliance requirements. By logging who did what and when, it provides security teams with critical insights into database activity.
We will soon be posting about auditing in Azure SQL! Stay tuned!
🔹 Have you implemented SQL Server Audit in your environment? Get started now: Check out more from Microsoft’s official documentation here: Microsoft Docs – SQL Server Audit