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);
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Kevin Kline

Career and Technical Advice for the IT Professional

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Powershellshocked

A blog about PowerShell and general Windows sysadmin stuff

Simon Learning SQL Server

I'm trying to become "better" at SQL Server and data - here's how I'm doing it!

%d bloggers like this: