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:

http://sqlserveruniverse.com/utility/images/article_images/ADMN01003_08.jpg

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.

]]>

Leave a comment

Your email address will not be published.