SQL Server Audit for application event - User-Defined Audit Actions

SQL Server Audit is the main tool for capturing events related to server and databases, and it can record event information to files, Windows Application Event Log, and Windows Security Event Log. All editions of SQL Server support server-level auditing but only Enterprise, Developer and Evaluation editions support database-level auditing. 

When defining an Audit, it needs to specify an Audit (1), then either Server-Specification or Database-Specification (2) specifying what Action/Action Group need to be included. Action/Action Groups are pre-defined comprehensive events that cover almost all events related to server and databases. Once configured, events are recorded with the given target. What if we want to raise our own events and get the captured with the same Audit?

Good example is, tracking changes in Product table, specifically on the Price column. If an Audit is already defined for other events, and need to use same target for recording price changes too, all we have to do is, include USER_DEFINED_AUDIT_GROUP Action Group to either Server or Database specification and use sp_audit_write stored procedure for recording our custom event. Here are steps for doing it.

Let's create an Audit first. If you have already created an Audit, you do not need to create it. Go to security node in the Object Explorer , then Audit node, and then create a new Audit.

Next, let's create a Server-Specification. Note that I have included two events; FAILED_LOGIN_GROUP and USER_DEFINED_AUDIT_GROUP. The second one added is for custom events.

Capturing ListPrice changes can be easily implemented with a Trigger and sp_audit_write procedure can be called within it. Here is the sample code for implementing it.

USE AdventureWorks2014;
GO

CREATE TRIGGER Production.CheckPriceChanged ON Production.Product
AFTER UPDATE
AS
BEGIN

 DECLARE @Message nvarchar(4000)
  , @OldPrice money
  , @NewPrice money
  , @ProductNumber nvarchar(25)

 IF UPDATE(ListPrice)
 BEGIN
 
  SELECT @OldPrice = ListPrice FROM deleted;
  SELECT @NewPrice = ListPrice, @ProductNumber = ProductNumber FROM inserted;

  SET @Message = N'Price changed: Product Number: ' +  @ProductNumber + N' Old price: ' + CONVERT(nvarchar(20), @OldPrice) + N' New price: ' 
      + CONVERT(nvarchar(20), @NewPrice) + N' User: ' +  CONVERT(nvarchar(20), SYSTEM_USER)

  -- Calling auditing stored procedure
  EXEC sp_audit_write 1, 1, @Message;
 END 
END;
GO

Let's make some changes and see how it is going to be recorded.

-- Updating records
UPDATE Production.Product
 SET ListPrice = 100
WHERE ProductID = 1;

UPDATE Production.Product
 SET ListPrice = 0
WHERE ProductID = 1;

-- Checking the audit output with fn_get_audit_file
SELECT event_time, object_name, user_defined_event_id, statement, user_defined_information
FROM sys.fn_get_audit_file ('c:\Audit\*',default,default);