Auditing Basics

The following example creates an Audit which will write to a file on the C:\ drive to record any Inserts or Selects against the AdventureWorks2008 sales.customer table

Create the Audit.

GUI: Instance | Security | Audits | New Audit

USE [master]
GO

/****** Object:  Audit [ServerAuditDB]    Script Date: 01/19/2012 12:30:19 ******/
CREATE SERVER AUDIT [ServerAuditDB]
TO FILE 
(    FILEPATH = N'C:\AuditSQL2008\'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = '8383f3a0-3f76-4d11-943d-5a504f7f7698'
)
GO

Then create a Database level Audit to monitor the Sales.Customer table for Select or Inserts

GUI: Instance | Databases | AdventureWorks2008 | Security | Database Audit Specifications | New ..

USE [AdventureWorks2008]
GO

CREATE DATABASE AUDIT SPECIFICATION [AdventureWorksDBAudit1]
FOR SERVER AUDIT [ServerAuditDB]
ADD (INSERT ON OBJECT::[Sales].[Customer] BY [dbo]),
ADD (SELECT ON OBJECT::[Sales].[Customer] BY [dbo])
WITH (STATE = ON)
GO

Make sure the Audits are enabled and then Test:

select * from sales.customer

View the Audit either by specifically selecting from the Audit file:

SELECT * FROM sys.fn_get_audit_file ('C:\AuditSQL2008\ServerAuditDB_8383F3A0-3F76-4D11-943D-5A504F7F7698_0_129714493641530000.sqlaudit',default,default);
GO

Or by Doing the following to select from all Audit files in the location:

SELECT * FROM sys.fn_get_audit_file ('C:\AuditSQL2008\*',default,default);
GO
SELECT 
event_time, 
succeeded, 
object_id, 
server_principal_name, 
server_instance_name, 
database_name, 
schema_name, 
object_name, 
statement FROM 
sys.fn_get_audit_file ('C:\AuditSQL2008\*',default,default);
GO

image

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: