Create an audit to monitor queries

As of SQL Server 2016 SP1, fine grained auditing is now available in Standard Edition. This has proved to be an easy way to monitor the querying of database objects. In my case, I use it to monitor SELECT queries on views in my data warehouse to determine which datasets are most popular with my users.

The following T-SQL will create the relevant audit.

1. Create the Server Audit:

USE [master]
GO
/* Set ServerAuditName, FILEPATH, MAXSIZE & MAX_ROLLOVER_FILES as required */
CREATE SERVER AUDIT [ServerAuditName]
TO FILE (
    FILEPATH = N'C:\AuditLogs'
    ,MAXSIZE = 1 GB
    ,MAX_ROLLOVER_FILES = 5
    ,RESERVE_DISK_SPACE = OFF
)
WITH (
    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
);
GO

2. Create the Database Audit Specification:

USE [DatabaseName]
GO
/* ServerAuditName should match the Server Audit created in the first step.
Set DatabaseAuditSpecificationName, ViewName_1 (etc), UserName as required. */
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecificationName]
FOR SERVER AUDIT [ServerAuditName]
    ADD (SELECT ON OBJECT::[DW].[ViewName_1] BY [UserName]),
    ADD (SELECT ON OBJECT::[DW].[ViewName_2] BY [UserName])
    /* Repeat the above line as many times as required to include all views. */
;
GO

3. Enable the Server Audit & Database Audit Specification:

USE [master]
GO
/* ServerAuditName should match the Server Audit created in the first step. */
ALTER SERVER AUDIT [ServerAuditName]
   WITH (STATE = ON);
GO

USE [DatabaseName]
GO
/* DatabaseAuditSpecificationName should match the Database Audit Specification created in the second step. */
ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecificationName]
   WITH (STATE = ON);
GO

To view the information captured in the audit logs, run the following query:

SELECT * FROM fn_get_audit_file('C:\AuditLogs\',default,default);