<

Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x

Audit in SQL Server 2008

Published on
18,693 Points
6,893 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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Join & Write a Comment

As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month