You are here:Home » tsql » Configure Server & Database Auditing for diffent Specification in SQL Server 2008

Configure Server & Database Auditing for diffent Specification in SQL Server 2008

-- Create Server Audit
USE [master]
GO
CREATE SERVER AUDIT [Audit-20091113-190623]
TO FILE
( FILEPATH = N'C:\Program Files\Microsoft SQL Server\New Folder\'
,MAXSIZE = 0 MB -- Unlimited
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = '163bdb6d-a067-4b2a-be26-6fdca5638762'
)
GO

--Create Server Audit Specification
USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20091113-191104]
FOR SERVER AUDIT [Audit-20091113-190623] -- SERVER AUDIT which created from above script
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = ON)
GO

--Create specific Database Audit Specification
USE [Repl]
GO
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20091113-191214]
FOR SERVER AUDIT [Audit-20091113-190623] -- SERVER AUDIT which created from above script
ADD (UPDATE ON OBJECT::[dbo].[policy] BY [public])
WITH (STATE = ON)