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.

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |