Audit in SQL Server 2008

Audit is really one of the interesting, useful yet difficult to maintain topic in history of SQL Server. I have already written few articles on how to maintain Audit trail in SQL Server. You can refer those articles from the below given links, once you will go through those articles and then read ahead this one, you will come to know how much it is easy in SQL Server 2008.

Audit Trail with Trigger

Keep Log of update in same table

Audit with OUTPUT clause

Earlier people had very few options for auditing in SQL Server but in latest technology, you are having quite a good power on auditing stuff. Since this is one of the big topics in SQL Server, it is not possible to cover it completely here, though I will try to give you one detailed example herewith below.

First of all when you want to use power of Audit in SQL Server 2008, you have to create AUDIT object. AUDIT object is nothing more than just a container of Audit specification on Server level as well as on database level. You can store log in Application Event Log, Security Event Log and Filesystem.

I am going to create one Audit object which will act as a container of one of the database level audit specification which will keep an eye on one Schema for any DML statement executed on that schema.

I will store log in D:\Audit in my server, you can change the path in given script if you wish, do create the folder and give reference in script below.

--Select MASTER Database

USE master

GO

--create SERVER Audit

CREATE SERVER AUDIT [FirstAudit]

TO FILE

(

      FILEPATH=N'D:\AUDIT\'

      ,MAXSIZE=10 MB

      ,MAX_ROLLOVER_FILES=100

      ,RESERVE_DISK_SPACE=ON

)

WITH

(

      QUEUE_DELAY=1000

      ,ON_FAILURE=SHUTDOWN

      ,AUDIT_GUID='2EB5EF64-1B15-4AFF-B248-6F39D423E2E7'

)

GO

--Alter server audit object for

--making it enable

ALTER SERVER AUDIT [FirstAudit]

WITH (STATE=ON)

GO

--creating one test database,

--if it is exists, droping it first

if exists(SELECT 1 FROM SYS.DATABASES WHERE name='AuditTest')

BEGIN

      USE master

      drop database AuditTest

END

CREATE DATABASE AuditTest

GO

--select newly created database

USE AuditTest

GO

--create two schema AUD1 and AUD2

CREATE SCHEMA Aud1

GO

CREATE SCHEMA Aud2

GO

--create two table

--one with each schema

if OBJECT_ID('Aud1.Table1') is not null drop table Aud1.Table1

CREATE TABLE Aud1.Table1 (id int)

GO

if OBJECT_ID('Aud2.Table1') is not null drop table Aud2.Table1

CREATE TABLE Aud2.Table1 (id int)

GO

--create database level AUDIT SPECIFICATION

--for our server audit created above

--which will keep watch on schema AUD1

--for DML statements but won't watch for AUD2 schema

CREATE DATABASE AUDIT SPECIFICATION [AuditTestSpec]

FOR SERVER AUDIT [FirstAudit]

ADD (SELECT,INSERT,UPDATE,DELETE ON SCHEMA::[Aud1] by [PUBLIC])

WITH (STATE=ON)

GO

--making some DML actions in both table

--of both schema

INSERT INTO Aud1.Table1

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5

go

INSERT INTO Aud2.Table1

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5

go

UPDATE Aud1.Table1 set id=6 where ID=5

GO

UPDATE Aud2.Table1 set id=6 where ID=5

GO

--looking at what we have received in our audit file.

--you will know that you got data for AUD1 schema only

SELECT * FROM sys.fn_get_audit_file('D:\Audit\*',DEFAULT,DEFAULT)

go

You can find detailed text on this topic from MSDN. Do look at all reference link given below the article in MSDN.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com