Server and Database Auditing in SQL Server 2008

SQL Server 2008 introduces a new feature called SQL Server Audit. Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. This feature enables Database Administrators to implement a strategy that they can use to meet the specific risks of their database environments.

SQL Server Audit Components
An AUDIT will be a combination of several elements into a single package for a specific group of server actions or database actions. A DBA can record server audit action group’s per-instance and either database audit action groups or database audit actions per database. The audit event will occur whenever the auditable action is encountered by the system. The four important components of SQL Server Audit’s are SQL Server Audit, Server Audit Specifications, Database Audit Specification and Target.

 

SQL Server Audit
The SQL Server Audit object collects a single instance of server or database-level actions and groups of actions to monitor. The audit captures data at the SQL Server instance level. However, DBA can configure multiple audits per SQL Server instance level. Once the audit is defined DBA also needs to specify the location for the output of results, which is also called as destination. By default when the audit is created it will be in the disable state. The audit destination receives data from audit only once the audit is enabled.

Server Audit Specification
The Server Audit Specification object is also part of an SQL Server audit. A DBA can create one server audit specification per audit, because both are created at the SQL Server instance scope. The server audit specification collects many server-level action groups raised by the Extended Events feature. Audit action groups are predefined groups of actions in SQL Server 2008; these are atomic events which are exposed by the Database Engine. These actions are sent to the audit which records them in the target. Users in the sysadmin role can tamper with any audit component on the SQL Server.