Auditing in SQL Server 2008

Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system.

Auditing declaration and executions steps:

1. Create a SQL Server Audit object.
2. Create an Audit Specification (can be at the server or database level).
3. Turn it on.

-- Create a SQL Server Audit object

CREATE SERVER AUDIT [ItaiAudit]

TO FILE 

( FILEPATH = N'D:\FolderA\'

,MAXSIZE = 100 MB

,MAX_ROLLOVER_FILES = 2147483647

,RESERVE_DISK_SPACE = OFF

)

WITH

( QUEUE_DELAY = 1000

,ON_FAILURE = CONTINUE

)

GO

-- Create an Audit Specification (database level)

CREATE DATABASE AUDIT SPECIFICATION [ItaiAuditSpecification]

FOR SERVER AUDIT [ItaiAudit]

ADD (UPDATE ON OBJECT::[dbo].[StamTable] BY [dbo]),

ADD (SELECT ON OBJECT::[dbo].[StamTable2] BY [dbo])

WITH (STATE = OFF)

GO


-- Another example: Create an Audit Specification to get logins (server level)
CREATE SERVER AUDIT SPECIFICATION [AuditSpec_itai_RG]
FOR SERVER AUDIT [Audit_itai_RG]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON)
GO

-- Turn it on

ALTER SERVER AUDIT ItaiAudit WITH (STATE = ON)

GO

-- Get auditing data from the file:
SELECT * 
FROM fn_get_audit_file('D:\FolderA\Audit*.sqlaudit',default, default) a

Note: Auditing is a SQL 2008 Enterprise version only feature.

msdn: Auditing in SQL Server 2008

http://msdn.microsoft.com/en-us/library/dd392015(v=sql.100).aspx

msdn: Understanding SQL Server Audit

http://msdn.microsoft.com/en-us/library/cc280386(SQL.100).aspx