SQL Server Audit Feature

— By Ginger Keys

Once upon a time there was an organization that depended on very accurate data entry rules.  For various reasons, some of the employees who were responsible for inputting and updating records were not very diligent about following rules to keep the data clean and uniform, reasoning that IT would go behind them and fix their mistakes.  True story.  Unsurprisingly, nobody claimed responsibility and the messy data continued to plague the databases.   Oh, if only the IT staff had known about and understood SQL Auditing…  There are also situations in which organizations are required by government regulations to audit changes to data.

So fast-forward to SQL 2008 and beyond, and a nice feature called SQL Server Audit is available.  If you need to know who has made changes to your database, your server, or objects, this tool will allow you to store and view the changes made.  SQL Audit is based on the extended events framework and events are recorded as soon as the permission-check for that event occurs.   Server level audits record events at the server level, such as creation or deletion of databases, logins, or linked servers, etc.  Database level audits record events at the database level, such as inserts, updates, stored procedure executions, dropping tables, etc.  Database level auditing is only available with Enterprise, Evaluation, and Developer editions.

SQL Server Audit is different from Change Data Capture (CDC) in multiple ways, including the fact that CDC does not record the user who made changes.  CDC was not meant for auditing, but rather provides historical information for changed data in a table.  CDC is not intended as an auditing tool, but more for supporting ETL processes, the features of which I will cover in another article.

The following steps demonstrate how to create a SQL Server Audit:

 1. Create the Audit

To create an audit for a database, connect to your instance through SSMS (SQL Server Management Studio) and expand the Security folder.  Right click on Audits and select New Audit…

There are several settings to configure for your audit.  Give your audit a Name, and select an Audit destination.  I have named the audit, selected a file location, and set the max rollover files to 15, and the max file size to 10MB as shown below.  (You should select your max rollover files and max file size dependent on your environment, disk space available, etc.)

The output of the audit (called the target) can be saved as event logs, or as a binary file.

If you choose to output your audit information to a Security Log or Application Log, these targets can be retrieved from either Windows Event Viewer (in Administrative tools on your server), or in View Audit Logs in SSMS (by right clicking on your audit located in the Security > Audits folder).  Special permission is required to write to the Windows Security Log…for more information, see: https://technet.microsoft.com/en-us/library/cc645889%28v=sql.105%29.aspx

If you choose a File location, you must specify the location of your file.  Depending on the events you decide to audit, the target files can potentially grow very large.  So be sure to review and archive them on a regular basis, and also be sure to limit access to these files only to privileged users.

 2. Create the Database Audit

After creating the audit in the Security folder of your instance, go to the Database you want to audit, open the Security folder, right click on Database Audit Specifications and select New Database Audit Specification…

To create audit specifications for your database, create a Name, select the Audit you previously created from the drop down box, and select the Audit Action Types:

The following provides a description of database-level audit action groups you can choose to specify what you want to audit in your database –  https://msdn.microsoft.com/en-us/library/cc280663.aspx

 3. Enable the Audit

After creating your Audit (under the Security folder) and Audit Specifications (under the Databases folder), you must enable both by right clicking on each, and selecting Enable

4. Verify the Audit

To test the audit, I executed both an UPDATE and DELETE statement on two different tables in my ABCompany database:

 USE ABCompany

GO

UPDATE Customers

SET contacttitle = ‘Marketing Mgr’

WHERE contacttitle = ‘Marketing Manager’

GO

DELETE FROM Orders

WHERE orderid = 10618

GO

To check the audit file to see who made what changes, we can run the following statement:

SELECT

LEFT(event_time, len(event_time) – 11) as date_time,

action_id,

succeeded,

session_id,

class_type,

session_server_principal_name,

server_principal_name,

server_instance_name,

database_name,

object_name,

statement

FROM sys.fn_get_audit_file

(

‘R:\Testing\*.sqlaudit’,default,default  –substitute your file location here

)

GO

The results show the date/time, the login name, database name, object name, the statement executed, along with other data:

For a complete description of all the columns in the sys.fn_get_audit_file function, click here https://msdn.microsoft.com/en-us/library/cc280765.aspx

Conclusion

SQL Auditing is a valuable feature that allows DBAs to enhance security and compliance, allowing organizations to log and track changes to data made by individual users and roles.  This article discussed a basic Database level audit.  For more in-depth information and to learn about Server level audits, go here https://msdn.microsoft.com/en-us/library/cc280386.aspx .

If you would like assistance configuring, maintaining, or monitoring your SQL Server and instances please contact us at SQLRx@sqlrx.com. We would be happy to answer any question you have, and would be interested to hear about your SQL experiences as well!  Visit us at www.sqlrx.com.