SQL Server Audit

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