<

Go Premium for a chance to win a PS4. Enter to Win

x

Audit in SQL Server 2008

Published on
18,655 Points
6,855 Views
8 Endorsements
Last Modified:
Awarded
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server.

In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace which is not the most intuitive tools. However in the latest editions, we are now given quite a lot of power for auditing with some purpose built tools. The SQL 2008 Audit is meant to be replacing SQL Trace, it is faster, more granular and easier to interact with from SQL Server Management Studio, and with code. In this article, I am using a T-SQL code approach.

Since this is one of the big topics in SQL Server 2008 (and bound to become more popular), it is not possible to cover it completely here. However, I will try to give you one detailed but simple example as an introduction to SQL Audit.

First of all when you want to use Audit in SQL Server 2008, you have to create a SQL Server AUDIT object. The Server AUDIT object is nothing more than just a container for defining the Target of the Audit specifications at the Server level and/or at the database level. You can store Audits in the Application Event Log, Security Event Log or Filesystem.

The general steps for creating and using an audit is (and you do need some privileges) :

Create a SQL Server Audit and define the target.
Enable the audit.
Create an Audit Specification at either the database or server level that maps to the audit.
Enable the audit specification.
Read the audit events by using the Windows Event Viewer, Log File Viewer, or the fn_get_audit_file function, depending on your Target.

In this Article we will go one more step and create a test database for the purposes of demonstrating the Audit facility. Best to play in a controlled environment first so you can "see" what happens and explore the possiblities. I will store my Audit log as a filesystem in D:\Audit on my server, you can change the path in given script if you wish, do create the folder and give reference in script below. 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.

--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 

-- From MSDN :
-- The results of an audit are sent to a target, which can be a file, the Windows Security event log, or the Windows Application event log. 
-- Writing to the Security log is not available on Windows XP. 
-- Logs must be reviewed and archived periodically to make sure that the target has sufficient space to write additional records.

Open in new window


Alter server audit object to enable it.
ALTER SERVER AUDIT [FirstAudit]
WITH (STATE=ON)
GO 

Open in new window


Now, lets create our test database, Be careful - we drop it first - so you will need to check the name
--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 

Open in new window


For our example, the next step is to create some database objects. For the purposes of our test, that will be two new schema and a table for each.
--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 

Open in new window


Right, now we have a new database with some database objects and have created our Audit server. So, let us now start the real business of applying an Audit Specification to the database. We will create an Audit to watch AUD1 but not AUD2.
--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 

Open in new window


Well, that was fairly easy. Now let us do some activity
--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 

Open in new window


Now come the interesting part. Looking up what we have captured in our Audit. To do this we are given a new function sys.fn_get_audit_file()
--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

Open in new window


While only a very brief overview, hope you can now see how much easier it is to get some very detailed Audit of your database without too much effort - just a few new concepts to learn and away you go. You do need to consider a few things when doing an Audit - not least of which is where it is going and the impact on disk. Indiscriminate Auditing can consume a lot of disk pretty quickly, and make it harder to find results afterwards.

You can find detailed text on this topic from Microsoft. Please look at the reference links given below before you begin :
MSDN :  http://msdn.microsoft.com/en-us/library/cc280386.aspx  
Technet :  http://technet.microsoft.com/en-us/library/dd392015.aspx
8
Comment
Author:RiteshShah
1 Comment
 
LVL 13

Expert Comment

by:vora_bhaumik
nice and really useful article. thanks.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Join & Write a Comment

Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month