Implementing SQL Server Audit in SQL 2008

Auditing a SQL Server instance is not a new operation. However, SQL Server 2008 Enterprise edition allows you to quickly and easily configure SQL Server Auditing to automate your tasks. This article covers how you can implement SQL Server Audit in SQL 2008.

Creating a SQL Server Audit

A SQL Server Audit is a security object that collects and logs either actions or groups of actions. It’s sort of like a hall monitor. It checks the halls and writes down what it sees, and pokes its head into the classroom windows (i.e., a databases) as well, if you tell it to do so. SQL Server Audits are always in a disabled state when created; much like hall monitors who need to be told when to report for their first day of work. You can create a SQL Server Audit with T-SQL or through SQL Server Management Studio (SSMS). Then, you can look for it under the Security folder where you will find a folder named ‘Audits’. Youshould create a server audit, not a database audit because the audit object is associated with the entire instance. You will next specify what exactly you want to audit, which will be either an instance or database specification, or both if desired.

Creating a Server Audit Specification

A Server Audit Specification can also be created with T-SQL or through SSMS. In SSMS, the folder is located right below the Audits folder. You can create only one Server Audit Specification per SQL Server Audit, but the specification can encompass multiple audit action groups, which just means that you define all of the instance level actions you want to be audited. When you create the specification, you can define the state to be either ON or OFF, unlike the SQL Server Audit, which will be OFF upon creation. The specification must be enabled before you can audit the action groups.

Creating a Database Audit Specification

A Database Audit Specification can be created for each database on the instance for each SQL Server Audit. This allows you to do a great deal of customization with different types of audits on different databases. A Database Audit Specification utilizes Extended Events to track and log events, which means you can add audit action groups or audit events to a Database Audit Specification. You can create a Database Audit Specification with T-SQL or through SSMS. You can find it in SSMS by going to your database and expanding the Security folder.

Enabling the SQL Server Audit

If you just love to use T-SQL, then use it to enable the SQL Server Audit. Otherwise, you can just go into SSMS, find the Audit, right-click, and enable it. Once it’s enabled, you can view events in a log file viewer, if the events to be audited have already occurred. For example, if you want to enable auditing on failed logins, you need to make certain there has actually been a failed login.SQL 2008 Security Audit is very easy to implement. There is no wizard, but you don’t need it because the architecture is relatively simple. The GUI itself will help lead you in the right direction. For example, you cannot create specifications unless a SQL Server Audit exists, which should be a big red flag to guide you in creating the SQL Server Audit first.