How to capture SQL database activities using SQL Audit.
<This functionality is not available in SQL Standard Edition>
-> Create Server level Audit.
EXEC sp_MSForEachDB 'If ''?'' not in ( ''master'',''msdb'',''model'',''tempdb'')
Begin
CREATE SERVER AUDIT [Audit-?]
TO FILE
( FILEPATH = N''I:\Test''
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT [Audit-?] WITH (STATE = ON)
END'
-> Create Database Audit Specification.
EXEC sp_MSForEachDB 'If ''?'' not in ( ''master'',''msdb'',''model'',''tempdb'')
Begin
USE [?];
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-?]
FOR SERVER AUDIT [Audit-?]
ADD (DELETE ON DATABASE::[?] BY [public]),
ADD (EXECUTE ON DATABASE::[?] BY [public]),
ADD (INSERT ON DATABASE::[?] BY [public]),
ADD (SELECT ON DATABASE::[?] BY [public]),
ADD (UPDATE ON DATABASE::[?] BY [public]);
ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-?]
WITH (STATE = ON)
End'
-> To read the Audit file.
SELECT * FROM sys.fn_get_audit_file ('I:\test\<filename>.sqlaudit',default,default);
GO
