SQL Server 2016 Auditing Improvements

By:   |   Comments (5)   |   Related: > SQL Server 2016


Auditing is an important mechanism for many organizations to serve as a checks and balances.  In SQL Server 2016 are there any new Auditing features to support the business and simplify how we audit?  Check out this tip to learn more.


The purpose of SQL Server database auditing is to audit database level activities such as INSERT, UPDATE, DELETE and even data access via the SELECT command. SQL Server 2016 provides the following new features for database auditing:

  • User - Defined Audit
  • Audit Filtering
  • Audit Resilience

Let's see how to implement each of these features in SQL Server 2016.

SQL Server 2016 User Defined Audit

In SQL Server 2016 we can configure custom logic and events to audit.  Below are the steps to configure user defined audit:

USE [master]

CREATE SERVER AUDIT [Audit_User_Defined_Test]
( FILEPATH = N'C:\mssqltips\Audit'
 ,MAXSIZE = 100 MB
 ,MAX_ROLLOVER_FILES = 2147483647
( QUEUE_DELAY = 1000

Alter Server Audit [Audit_User_Defined_Test] with(State=ON)

We can use SQL Server Management Studio to configure a Server Audit by navigating to the Security folder then right clicking on Audit-> Create New Audit.

Setup a SQL Server 2016 Security Audit

SQL Server 2016 Database Level Audit

Below is code to create a SQL Server 2016 Database Level Audit:

Use [AdventureWorks2016CTP3]

Create Database Audit Specification Test_database_audit
for server audit [Audit_User_Defined_Test]
ADD (User_Defined_Audit_Group)

We can view the database audit properties by expanding the Adventureworks database and go to Security then Database Audit Specifications, then right click on Audit properties.

SQL Server 2016 Database Audit Specification Properties

We can see that the Audit action type is set to User_Defined_Audit_Group which basically tracks events raised by the sp_audit_write stored procedure.

  • Trigger to write an audit record using sp_audit_write
  • Suppose we want to audit the Adventureworks database table [Production].[ProductListPriceHistory] which is used to store the price of all the products and their history records. Sales people previously modify the price of the products based on the requirements however we want to audit if anyone has reduced the price by more than 20%.

    Now we will write a trigger to check this condition and write the data to audit files.

    Create Trigger [Production].[ProductListPrice] 
    on [Production].[ProductListPriceHistory]
    After Update
    declare @OldListPrice money
    ,@NewListPrice money,
    @productId int,
    @msg nvarchar(2500)
    select @OldListPrice=d.ListPrice
    from deleted d
    select @NewListPrice= i.ListPrice , @productId=i.ProductId
    from inserted i
    If (@OldListPrice*0.80 >@NewListPrice)  -- implement logic condition
     Set @msg='Product '+ Cast (@productid as varchar(50))+' ListPrice is decreased by more than 20%' --print message to be logged
     Exec sp_audit_write @user_defined_event_id=27,
     @succeeded =1, 
     @user_defined_information = @msg;

    Now we will update the price of the product and see if this is logged into the events

    Review the historical records captured for the Production.ProductListHistory table

    Now if we go to View audit logs in SQL Server Management Studio under Security->Audit->Audit_User_Defined_Test, we can see the price changed meet our trigger condition and it is recorded in Audit logs.

    Review the SQL Server 2016 Audit Logs to see that the product price increased by more than 20 percent

    As we can see in the logs it gives detailed information with database name, server name, object name, statement along with lot of useful information.  Similarly we can configure the audit as per our requirement and this could be quite useful functionality.

    SQL Server 2016 Audit Filtering

    Audit filtering allows the filtering of unwanted audit events before they are written to the audit log. As part of an auditing policy, we can log which users are accessing selected tables that contain sensitive data.

    Suppose we want to audit the Employee information table for SELECT and UPDATE statements, for this we will create new server audit and database audit as shown below:

    Use Master --server Audit needs to be created in the Master database
    Create Server Audit Audit_Security_Employee
    To file (FilePath='C:\mssqltips\Audit');
    Alter Server Audit Audit_Security_Employee with(STATE=ON)
    Use [AdventureWorks2016CTP3]
    Create Database Audit Specification Audit_Employee
    for Server Audit Audit_Security_Employee
    Add (Select , update  on [HumanResources].[Employee]  by dbo) --Insert condition for which event to be tracked.
    With (State=ON);

    Now let's execute both SELECT and UPDATE operations to track the audit events.

    SELECT * 
    FROM [HumanResources].[Employee]
    UPDATE [HumanResources].[Employee] 
    SET NationalIDNumber=987654321 
    WHERE BusinessEntityID=1

    Now we will view the audit logs and see if it is logged.

    Review the Audit Logs to see the Employee table was queried

    Update to the NationalIDNumber in the Employee table

    Second UPDATE to the NationalIDNumber

    As we can see in the images above both the events are logged in the audit logs.  This could be also very useful if we want to see who has accessed the particular data with detailed information logged into event logs.

    SQL Server 2016 Audit Resilience

    SQL Server 2016 Audit Resilience gives us the ability to recover auditing data from a temporary file in case network issues occur, this will ensure audit logs are not lost during failover. SQL Server will automatically recover the data in such situations.

    The next item to note is that SQL Server 2016 also audits all enable and disable events.

    To check this functionality, disable the audit feature and then enable if after some time.

    Disable SQL Server 2016 Auditing

    Enable SQL Server 2016 Auditing

    Now we will review the Audit logs to verify the events are captured.

    Event Log entries indicating the auditing has been disabled

    Event Log entries indicating the auditing has been disabled and destroyed

    SQL Server 2016 Audit has been restarted

    We can see here that both the Disabled Audit and Enable Audit events are logged into the Audit Logs. There are 2 entries for the disable audit as well as disable and destroy audit which basically means audit has stopped logging into the audit logs.

    Next Steps

    sql server categories

    sql server webinars

    subscribe to mssqltips

    sql server tutorials

    sql server white papers

    next tip

    About the author
    MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

    This author pledges the content of this article is based on professional experience and not AI generated.

    View all my tips

    Comments For This Article

    Monday, February 3, 2020 - 3:09:04 PM - Seth Back To Top (84127)

    Thanks Rajendra.

    Tuesday, January 28, 2020 - 5:10:39 AM - Rajendra Back To Top (84008)

    Hi Seth

    I will check for the Microsoft documentation on this and will share with you.

    Regards ~Rajendra

    Monday, January 27, 2020 - 6:07:20 PM - Seth Back To Top (84002)

    Hi Rajendra,

    Thanks for this article.  I'm looking for specific Microsoft documentation (for compliance purposes) that covers the 'Audit Resilience' you speak of - do you know where I can find that?

    Thank you

    Monday, April 2, 2018 - 7:56:59 AM - Nirav Back To Top (75576)

    Hello ,

    Thanks you very much. very usefull.

    I have one question that is there any way to identity the number of records updated or delated.



    Wednesday, November 30, 2016 - 9:15:39 AM - Jach Back To Top (44866)

    Great article. But I noticed you censored your user and instance name in the audit data, so I thaught I should tell you that you didn't censor it in the first picture of it.

    get free sql tips
    agree to terms