/*STEP1 Create server Audit specification*/
USE [master]
GO
CREATE SERVER AUDIT [TestAudit]
TO FILE
( FILEPATH = N'D:\temp\' /*Specify the folder path here*/
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
GO
/*STEP 2 Create Database Audit specification*/
USE [MyDB]GO
CREATE DATABASE AUDIT SPECIFICATION [TestDBAudit]
FOR SERVER AUDIT [TestAudit]
ADD (DELETE ON OBJECT::[dbo].[MyTbl] BY [db_owner]), /* Object name is the table name */
ADD (UPDATE ON OBJECT::[dbo].[MyTbl] BY [db_owner]), /* Object name is the table name */
ADD (INSERT ON OBJECT::[dbo].[MyTbl] BY [db_owner]) /* Object name is the table name */
WITH (STATE = OFF)
GO
/*STEP 3 Enable database Audit*/
USE [MyDB]GO
Alter DATABASE AUDIT SPECIFICATION [TestDBAudit]
WITH (STATE = ON)
GO
/*STEP 4 Enable Server Audit*/
USE [master]GO
Alter SERVER AUDIT [TestAudit]
With (STATE = ON)
GO
/*STEP 5 View the audit file */
SELECT * FROM sys.fn_get_audit_file(N'D:\temp\<FileName>.sqlaudit',
default,default);
GO
