SQL Server Audit User-Defined Audit Events

SQL Server Audit – User-defined Audit EventsOriginally published on ColleenMorrow.com.

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]

CREATE SERVER AUDIT [TestingUserDefinedEvents]
(   FILEPATH = N'D:\SQL2012\Audits'
    ,MAXSIZE = 5 MB
(   QUEUE_DELAY = 1000
ALTER SERVER AUDIT [TestingUserDefinedEvents] WITH (STATE = ON);

USE [AdventureWorks2012]

FOR SERVER AUDIT [TestingUserDefinedEvents]


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]
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

CREATE TRIGGER [humanresources].[SalaryMonitor] ON [humanresources].[employeepayhistory]
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
    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;

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!



About the Author

SQL Server Consultant

Colleen Morrow

UpSearch Alum Colleen Morrow is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft's SQL Server to protect, optimize and unlock data's value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

About UpSearch


UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *