How to start and set up SQL Server Audit

Get an overview of the SQL Server Audit process, and check out the details for defining a target, setting up specifications and reading logged events.

SQL Server Audit, now native to SQL Server, is integrated into SQL Server Management Studio (SSMS). This provides an easy interface for detailed auditing, which facilitates the tracking and logging of events in the SQL Server database engine.

Although a detailed description of how to implement an audit in SQL Server is beyond the scope of this article, the following steps provide an overview of the SQL Server Audit process:

  1. Create a server audit that defines the target.
  2. Create the necessary server and database audit specifications.
  3. Enable the server audit and the specifications.
  4. Read the events once they've been recorded in the target.

You can read the events in Windows Event Viewer if you configured your server audit to point to the Windows Security or Application event log. You can also use Log File Viewer in SSMS to view the Windows event logs or to view the audit binary files, if you configured your SQL Server audit to use files as your target. In addition, you can use the sys.fn_get_audit_file system function to retrieve the event data from the binary files.

Regardless of which target type you choose, SSMS makes the process of creating an audit quite simple in Object Explorer:

  • Create the server audit in the Audits subfolder of the Security folder.
  • Create the server audit specification in the Server Audit Specifications subfolder (also under Security).
  • Create the database audit specification in the Database Audit Specifications subfolder of the Security folder associated with the target database.

For the most part, to create one of these objects, you simply right-click the appropriate subfolder and follow the prompts. SQL Server does the rest.

Figure 1 below shows Object Explorer in SSMS with several SQL Server Audit components defined: TestServerAudit, TestServerAuditSpec and TestDatabaseAuditSpec. The database audit specification is on the AdventureWorks2012 database.

Object Explorer in SSMS
Figure 1: An audit is made up of a server audit and one or more specifications.

Although you can't tell from Figure 2, the server audit in this case is configured to use binary files as the target, and the specifications are each configured to use the SCHEMA_OBJECT_CHANGE_GROUP action group. That means, for the server audit specification, events are raised for any CREATE, ALTER or DROP operations on server objects, such as dropping a principal; but for the database audit specification, events are raised for any CREATE, ALTER or DROP operations at the schema level, such as adding or altering a view.

If, based on these settings, we were now to run a simple CREATE TABLE statement and DROP TABLE statement against the AdventureWorks2012 database, we could view the results in the Log File Viewer, as shown in Figure 2 below. Notice that an event is shown for the DROP statement and one for the CREATE statement. The third event is related to the auditing operation itself.

Figure2: SQL Server Audit data includes details about each event included in the specifications.

Log File Viewer displays a variety of information about each event. It might be difficult to read here, but when you try SQL Server Audit for yourself, you'll see how easy it is to record and view information about how your database is being used. And if you're already using SQL Server Audit, you'll find that, for the most part, little has changed from SQL Server 2008 to 2012, except for a few added features. For example, you now have more ways to configure how the system reacts upon audit failure, and you can now create filters within a server audit, something not possible in 2008. But the basic functionality has remained unchanged.

Of course, when you implement SQL Server Audit, you must take into account a number of considerations not covered here, such as the security of your logged data, its impact on performance when auditing data and how audits are treated when attaching a database or creating a database mirror. But once you sort through these issues, you'll find the basic SQL Server Audit components are simple to implement, easy to manage and effective in auditing your database. Together, they help ensure data compliance and protection. So if you're still hanging onto SQL Trace and those other tools for auditing, now is as good a time as any to consider making the switch.

About the author:
Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at http://www.rhsheldon.com.

This was first published in July 2013

Dig deeper on Microsoft SQL Server Tools and Utilities

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close