Professional Microsoft® SQL Server® 2008 Administration

9.9. SQL Server Audit

SQL Server 2008 Enterprise Edition introduces increased auditing capabilities through the use of SQL Server Audit. SQL Server Audit can track and log events that occur at the server level or the database level automatically. It does this through an Audit object.

9.9.1. Creating an Audit

An Audit object is a collection of one more individual actions or a group of actions to be tracked. For instance, you can configure an Audit object to track all failed logins. It writes all the events to a location you specify: the Application event log, the Security event log, or to a file.

You can create an Audit object using either Management Studio or T-SQL. To do this using SSMS, start by right-clicking on the Audits folder under the Security tree and selecting New Audit. You'll next need to give the Audit object a name and specify where it will write its information to. Give it the name Audit-FailedLogins and set it to write to a file. Specify the path where you want the file stored, as shown in Figure 9-18. Click OK to create the Audit object and you should now see it appear under the Audits folder. Create a second Audit object called Audit-EmployeeQueries. We'll use it to track SELECT statements issued against the HumanResources.Employee table in the AdventureWorks2008 database.

Figure 9-18. Figure 9-18

If you want to create an audit using T-SQL, you can do so by ...