Auditing in SQL Server 2008

Firstly, create a new Audit and configure the audit file location. You can configure this in two ways, by using SQL Server Management Studio (SSMS) or by using T-SQL.

By SSMS - Option is undr security tab, Create a new audit and then enable it.

For TSQL - Use below code

============
USE [master]
GO
/****** Object: Audit [Audit-20091125-113200] Script Date: 11/25/2009 11:57:07 ******/
IF EXISTS (SELECT * FROM sys.server_audits WHERE name = N'Audit-20091125-113200')
DROP SERVER AUDIT [Audit-20091125-113200]
GO
USE [master]
GO
/****** Object: Audit [Audit-20091125-113200] Script Date: 11/25/2009 11:57:07 ******/
CREATE SERVER AUDIT [Audit-20091125-113200]
TO FILE
( FILEPATH = N'C:\Audit\'
,MAXSIZE = 2048 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = SHUTDOWN
)
GO

============

Auditing Specifications -->

Now we have created the audit location, we need to create the auditing specifications. There are two types of auditing specifications.

1. Server Audit Specification
2. Database Auditing Specification

Server Audit Specification can be found under security tab.
or
use below TSQL to create it:

===========
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'ServerAuditSpecification-20091126-130056')
DROP SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20091126-130056]
GO
USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20091126-130056]
FOR SERVER AUDIT [Audit-20091125-113200]
ADD (BACKUP_RESTORE_GROUP),
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = OFF)
============

Database Auditing Specification

It is available under Database_Name--> Security tab
or
use below TSQL to create it-->

============
USE [AdventureWorks2008]
GO

IF EXISTS (SELECT * FROM sys.database_audit_specifications WHERE name = N'DatabaseAuditSpecification-20091126-130626')

DROP DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20091126-130626]
GO

USE [AdventureWorks2008]
GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20091126-130626]
FOR SERVER AUDIT [Audit-20091125-113200]
ADD (DELETE ON DATABASE::[AdventureWorks2008] BY [dbo]),
ADD (INSERT ON DATABASE::[AdventureWorks2008] BY [dbo])
WITH (STATE = OFF)
GO
============