One thing I failed to touch on during my series on SQL Audit was the use of user-defined events in audits. This was brought to my attention in a comment by one of my readers. He was trying to make use of user-defined events and was having a problem getting the output to actually write to the audit file. Since I was writing some code to recreate the problem on my system anyway, I decided to post it here.

Why might you want to create a user-defined event audit in the first place? Well, we already know that we can use SQL Audit to audit access to certain objects. So let’s say we have a table with salary data. We can use the SCHEMA_OBJECT_ACCESS_GROUP to audit access to any object in that schema. We can also use SELECT, INSERT, UPDATE, and DELETE actions to audit those actions on specific objects. But let’s say even getting that granular will produce more audit output than we’d like. Suppose we only want to know when an employee’s salary is increased by more than 10%. We can’t do that with any of the canned actions. But we can do that with a custom event.

Configure the audit

The first step is to configure the audit object. Once we’ve got that configured we create the audit specification. This can be either a server audit spec or a database audit spec, depending on your needs. Just make sure to add the USER_DEFINED_AUDIT_GROUP action. And don’t forget to enable both the server audit and the audit spec.

 USE [master]
GO
CREATE SERVER AUDIT [TestingUserDefinedEvents]
TO FILE
(FILEPATH = N'D:\SQL2012\Audits'
,MAXSIZE = 5 MB
,MAX_ROLLOVER_FILES = 5
,RESERVE_DISK_SPACE = OFF
)
WITH
(QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
GO
ALTER SERVER AUDIT [TestingUserDefinedEvents] WITH (STATE = ON);
GO
USE [AdventureWorks2012]
GO
CREATE DATABASE AUDIT SPECIFICATION [UserDefinedEvents]
FOR SERVER AUDIT [TestingUserDefinedEvents]
ADD (USER_DEFINED_AUDIT_GROUP)
WITH (STATE = ON)
GO
 

Writing to the audit

To write to the audit log, we’ll use the sp_audit_write stored procedure. This built-in stored procedure accepts 3 parameters:

• @user_defined_event_id is a smallint used to identify the event
• @succeeded is a binary flag used to specify whether the action was successful or not
• @user_defined_information is an nvarchar string describing the event

So, to test our audit, let’s run the following:

 USE [AdventureWorks2012]
GO
EXEC sp_audit_write @user_defined_event_id =  27 ,
              @succeeded =  0
            , @user_defined_information = N'Testing a user defined event.' ;
 

If we check the audit log, we should see the event.

Putting it into practice

Back to our original purpose, we wanted to know whenever an employee’s salary was increased more than 10%. To do this, we can create a trigger. (As I’ve mentioned many times in the past, I’m not a developer, so no fair picking on my trigger code.)

 USE AdventureWorks2012
GO
CREATE TRIGGER [humanresources].[SalaryMonitor] ON [humanresources].[employeepayhistory]
AFTER UPDATE
AS
declare   @oldrate money
, @newrate money
, @empid integer
, @msg nvarchar(4000)
select@oldrate = d.rate
from deleted d
select @newrate = i.rate, @empid = i.BusinessEntityID
from inserted i
IF @oldrate*1.10 < @newrate
BEGIN
SET @msg = 'Employee '+CAST(@empid as varchar(50))+' pay rate increased more than 10%'
EXEC sp_audit_write @user_defined_event_id =  27 ,
              @succeeded =  1
            , @user_defined_information = @msg;
END
GO
 

Now if we test the trigger by virtually doubling employee 4′s rate and only increasing employee 8′s rate by a small amount.

 select * from HumanResources.EmployeePayHistory where BusinessEntityID= 4
Update HumanResources.EmployeePayHistory set rate = 59.8462
where BusinessEntityID=4 and RateChangeDate = '2006-01-15 00:00:00.000'
select * from HumanResources.EmployeePayHistory where BusinessEntityID= 8
Update HumanResources.EmployeePayHistory set rate = 41.8654
where BusinessEntityID=8 and RateChangeDate = '2003-01-30 00:00:00.000'
 

We should see an audit record for employee 4 in the output, and we do.

You might be wondering if you can configure an audit to only capture specific user-defined event IDs.  Great question, and you can by filtering on the user_defined_event_id field in the server audit.

So there you have it, another way to tailor SQL Audit a bit more to your specific needs. Happy auditing!