Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
We will create sql server audit step by step in this article. We need to create an Audit file to record transactions made by people and dbas in SQL Server.
Create SQL Server Audit
First, right-click Audits in the Security section of SSMS and click on the new audit.
We give a name to the AuditName section. I prefer to give the name of the database I created(AuditDBName). Because I will create the audit database-based.
SQL Server Auditing Best Practices
Do not make any changes on “queue delay” and “On Audit Log Failure” sections.
If you select “shut down server” from the “On Audit Log Failure” section, sql server service will stop if the audit doesnt work. You can select this option if you do not want the database to work when the audit does not work. This option can be selected in applications where security is very high.
Audit Destination will remain as File.
In the File Path section, we specify the path to write the Audit File.
You can save Audit to the local server where your instance is running. But in order to keep the DBA’s own security and keep all DBAs under control, it is better to write the audit to a share on a remote server.
On the share on the remote server, the sql server service account must have only the insert and modify authority. There should not be any rights to drop and delete. In this way, a malicious dba will not be able to modify or delete the audit file.
Do not make any changes on the “Audit File Maximum Limit” section.
I usually set Maximum File Size specifically.
If the audit size does not exceed 100 MB in 1 month, I set it to 100 MB.
If the audit size will exceed 100 MB in a week, I set it to 1000 MB.
We do not choose Reserve disk space because it unnecessarily allocates disk space before writing audit.
You can also filter the audit from Filter option.
You can see these operations below.
SQL Server Audit Specification
We can create two types of audit specification.
- Database Audit Specification
- Server Audit Specification
Create Database Audit Specification
Right-click on the “Database Audit Specification” in the Security tab of the database, and click “New Database Audit Specification”. We give a name from the Name field. We choose the audit we created from Audit section.
SQL Server Audit Action Type For Database Audit Specification
Then, in the “Audit Action Type” section, we select the sql statement that we want to audit, “DATABASE” from the object Class section, database name from the Object Name section, and the user from the Principal Name section.
As a critical knowledge; To watch sysadmin on Instance, you must select dbo in the Principal Name section.
As you can see below, we will audit the selects, updates and deletes performed by sysadmin and updates and deletes performed by denemeUser.
SQL Server Audit User Activity
You can chose specific user from PrincipalName and chose specific audit action type for those users when creating Database Audit Specification.
SQL Server Audit Select Statements
Normally we dont audit select statements, because most application sends a lot of select and fill audit log files. But you can audit select statements for speficis users. Look at the above image first. Then Select “SELECT” from Audit Action Type and “DATABASE” from Object Class , your database name from ObjectName and specific login name you want to audit from PrincipalName.
SQL Server Audit Table Data Changes
Like Select statements we have mentioned above you can audit table data changes by using Database Audit Specification. Use UPDATE and DELETE instead of SELECT as action type to perform this operation.
Create Server Audit Specification
We do a new audit file configuration by repeating the first operations we performed in the “Database Level Audit” section.
Then right click on Server Audit Spesification from Security Tab and click on new server audit specification.
We give a name from the Name section and select the audit we created.
SQL Server Audit Action Type For Server Audit Specification
And we choose action from action type and click ok. Here you can follow a lot of things on server level.
For example, failed logins, changes on schemas, changes on audits.
I’m using almost all server-level actions.
In this way, we record all the work done by the database administrators at the server level.
For example, if a malicious dba stops the select audit in that database to take a select from a database that should not be read, you can create another audit for logging it.
Or, you can create an audit when a login with a select right is created in a database that should not be read.
In this way, you can follow every step taken by everyone and every breath they take. This includes you. Of course, a dba provides his/her own security by creating an audit system.
SQL Server Audit Log
To see the SQL Server audit log, right click on the relavant Audit file and then click View Audit Logs.
Filtering SQL Server Audits
Sometimes you may want to use filters when creating Server Audit Specification or Database Audit Specification. You can use below commands for specific columns on the audit file you SELECT
You can SELECT the audit log files we created with the help of the below article.
“How To Read SQL Server Audit”
Filter SQL Server Audit Files
1 2 3 4 5 | ALTER SERVER AUDIT ServerAudit WITH (STATE = OFF); GO ALTER SERVER AUDIT ServerAudit WHERE column_name_in_the_audit_file <> N'your_filter'; GO ALTER SERVER AUDIT ServerAudit WITH (STATE = ON); |
1 2 3 4 5 | ALTER SERVER AUDIT ServerAudit WITH (STATE = OFF); GO ALTER SERVER AUDIT ServerAudit WHERE object_name_in_the_audit_file <> N'my_table_name_will_be_exluded'; GO ALTER SERVER AUDIT ServerAudit WITH (STATE = ON); |
SQL Server Audit Performance Impact
Yes, of couse SQL Server auditing has an impact about performance. If your application is using database frequently it decreases performance. I have 12 years of experience about SQL Server and I can not use audit if the database is a database heavily used. However, this is a very rare occurrence. Sometimes you can see wait types about Audits in currently running queries. Look at the article; “Currently Running Queries in SQL Server”
You can create a policy to detect disabled SQL Server audits with the help of the below article.
“How To Check Disabled Audits Using Policy Based Management”
Below is a link to the details of server level audit actions.
Remember to enable audits and audit specifications.
https://msdn.microsoft.com/en-us/library/cc280663.aspx
