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!
Hi, do you know what audit group i would need to add to my audit to capture the use of sp_configure? I’m removing an old events based audit system we had in production. I’m replacing it with SQL audit.
However i’m struggling to capture events like ALTER_INSTANCE, which usually ties up with somone running sp_configure and CREATE_EXTENDED_PROCEDURE (Applies to sp_addextendedproc.).
Is there an audit group i can add that will capture someone running these procs and thus the events……? or would i have to try to come up with a trigger and use USER_DEFINED_AUDIT_GROUP?
Hi Jay – you could just create a database audit spec in master that tracks execute events on sys.sp_configure to capture instance configurations changes. Auditing the SERVER_OPERATION_GROUP would only capture the RECONFIGURE statement, which isn’t all that helpful for knowing what configuration change was actually made. Creation of an extended stored proc would fall into the SCHEMA_OBJECT_CHANGE_GROUP.