Auditing in SQL Server 2008

Introduction to SQL Server Auditing

SQL Server's auditing capabilities were significantly upgraded with the release of SQL Server 2008, to the great relief of database administrators and security professionals alike. The new auditing capabilities allow you to track user and system activity in a manner that is compliant with recently enacted security regulations, including the Health Insurance Portability and Accountability Act (HIPAA) and the Payment Card Industry Data Security Standard (PCI DSS).

There are two steps to enabling auditing in SQL Server 2008:

1. Create an audit object. The audit object contains the logistic details about your audit, such as the location where SQL Server will store your audit results, how much space it may use for the audit and what should happen if the server experiences an auditing failure.
2. Create an audit specification. The audit specification contains the technical details of the audit: what you will audit. You may create either a server audit specification, which audits server-level activities, or a database audit specification, which audits database-level activities.

Creating a SQL Server Audit Object

Creating an audit object in SQL Server 2008 is very straightforward and takes only a few minutes. Here's the process:

1. Open SQL Server Management Studio and connect to the applicable instance of Microsoft SQL Server
2. Expand the Security folder
3. Right-click on the Audits folder and select New Audit
4. Name your audit object by filling in the Audit Name field
5. Select a destination for your audit results. You may send them to a file, the Windows Application log or the Windows Security log.
6. Click the OK button to create the audit object

Creating a SQL Server Server Audit Specification

If you wish to audit server-level activities, such as successful and failed logins, you'll need to create a server audit specification and link it to the audit object you just created. Here's the process:

1. Right-click on Server Audit Specifications in the Security folder of SSMS
2. Select New Server Audit Specification from the pop-up menu
3. Name your audit specification in the Name field
4. Select an audit object from the drop-down list in the Audit field
5. Use the grid to select the server actions you wish to audit
6. Click the OK button to create the server audit specification and begin auditing

Creating a SQL Server Database Audit Specification

If you wish to audit database-level activity in addition to (or in place of) server-level activity, you'll need to create a database audit specification. Here's how to do that:

1. Expand the Databases folder in SSMS
2. Expand the folder corresponding to the database you wish to audit
3. Expand the Security folder contained within the database's folder
4. Right click on Database Audit Specifications in the Security folder
5. Select New Database Audit Specification from the pop-up menu
6. Name your audit specification in the Name field
7. Select an audit object from the drop-down list in the Audit field
8. Fill in the grid specifying the actions you wish to audit. You may click on the ellipses ("...") buttons in the Object Name and Principal Name fields to pull up a list of possible items
9. Click the OK button to create the server audit specification and begin auditing