Audit in SQL Server 2008

Friday, November 20, 2009 |

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.

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

2 comments:

Unknown said...

one main reason I love this website is, it has really simple, useful practical example of difficult stuff in easy manner, anybody can use it directly in production.

Ritesh Shah said...

related post http://www.sqlhub.com/2011/07/audit-trail-with-output-clause-in-sql.html