Auditing in SQL Server 2008

Database Auditing Specification

Database auditing is to audit database specific activities. Similar to Server Audit Specification there is a new Database Auditing Specification node below the DatabaseName -> Security node.

By right clicking and select new Database Auditing Specification, you can create a Database Auditing Specification. Next select an appropriate audit action group. The following list gives you the commonly used auditing groups.

SELECT	This event is raised whenever a SELECT is issued.
UPDATE	This event is raised whenever an UPDATE is issued.
INSERT	This event is raised whenever an INSERT is issued.
DELETE	This event is raised whenever a DELETE is issued.
EXECUTE	This event is raised whenever an EXECUTE is issued.
REFERENCES	This event is raised whenever a REFERENCES permission is checked.

Apart from the above list, all the groups in the Server Audit Specifications which starts with DATABASE are available. The main difference is that, Database Auditing Specification is for a given database while Server Audit Specification is for an entire server.

USE [AdventureWorks2008] GO IF  EXISTS (SELECT * FROM sys.database_audit_specifications WHERE name = N’DatabaseAuditSpecification-20091126-130626′) DROP DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20091126-130626] GO USE [AdventureWorks2008] GO CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20091126-130626] FOR SERVER AUDIT [Audit-20091125-113200] ADD (DELETE ON DATABASE::[AdventureWorks2008] BY [dbo]), ADD (INSERT ON DATABASE::[AdventureWorks2008] BY [dbo]) WITH (STATE = OFF) GO

As for Server Audit Specification, we create a Database Auditing Specification from T-SQL.

The below is a screenshot of the data generated:

As with Server Auditing Specification, you need to enable Database Auditing Specification and disable it  if you need to alter or drop the Database Auditing Specification.

Supported Editions

The Audit feature is supported only in Enterprise and Developer editions of SQL Server 2008.