5 ways to track Database Schema changes – Part 5 – SQL Server Audit

5 ways to track database schema changes - part 5 - SQL Server Audit

This is the fifth post from my short 5 part series in which I demonstrate you 5 different ways to track database schema changes. Thanks to this, you always can easily answer questions about what has changed, who changed it and when. Today, I describe how to use SQL Server Audit for this purpose.

Here you can find my previous posts from this series:

SQL Server Audit

The fifth option is SQL Server Audit. This functionality has been introduced in SQL Server 2008. It is based on Extended Events and allows you to audit SQL Server instance or an individual database. The results of an audit can be saved to a file, the Windows Security event log, or the Windows Application event log. The audit can be created to log a single event or events from a predefined event group. Sounds familiar? This mechanism is very similar to that used with DDL Triggers.

The SQL Server Audit consists of several elements:

  • The SQL Server Audit – It is created at SQL Server instance level. Specifies the destination where captured events are being stored.
  • The Server Audit Specification – belongs to SQL Server Audit. It is created at SQL Server instance level and collects many server-level action groups raised by Extended Events.
  • The Database Audit Specification – belongs to SQL Server Audit. It is created at the database level and collects many database-level action groups raised by Extended Events.

To test this solution, we must create an SQL Server Audit that captures the group of object modification events. In our case it is:


This group tracks create, alter, and drop events on schema and its objects like table, view, stored procedure, function, etc.

Creating an SQL Server Audit

As a first step, we have to create the SQL Server Audit object. It can be done using Wizard or using SQL. To use GUI, in Object Explorer expand Instance and then Security. Right Click on “Audits” and choose “New Audit…”.

Audit - Start Wizard

In the “Create Audit” window provide the audit name, destination and the data retention. Then click “OK”.

Audit - Create Audit

By default, the audit is created in a disabled state.

Audit - Audit is created

As a next step, we must create Audit Specification. If you want to log object schema change events in all databases, then it will be Server Audit Specification. If you’re interested in logging only events from one particular database, then it will be Database Audit Specification.

For demo purposes, I choose the Database Audit Specification. To create it,  in Object Explorer, expand Instance -> Databases -> DB -> Security. Right click on “Database Audit Specifications” and choose “New Database Audit Specification…”.

In the “Create Database Audit Specification” window we need to do a few things. Provide the name for the new Database Audit Specification. Then select the audit created in the previous step. Finally, choose actions (or groups of actions) that should be traced. In our case, it is SCHEMA_OBJECT_CHANGE_GROUP. Then click “OK”.

Audit - Create DB Audit Specification

By default, the Database Audit Specification is created in a disabled state. To start auditing, we must enable it and then enable SQL Server Audit.

The same steps can be done with the usage of T-SQL. You can use this script to create such SQL Server Audit together with Database Audit Specification.

USE [master]

CREATE SERVER AUDIT [ObjectsModification]
(	FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SS2017\MSSQL\Log\'
    ,MAXSIZE = 10 MB
(	QUEUE_DELAY = 1000

USE [TrackMyChanges]

FOR SERVER AUDIT [ObjectsModification]

ALTER SERVER AUDIT [ObjectsModification] WITH (STATE = ON)
Viewing logged events

To view logged events in SSMS right-click on the SQL Server Audit you want to check, and choose “View Audit Logs”.

Audit - View Audit Logs

What was captured by SQL Server Audit?

SQL Server Audit works similarly to the Extended Events Sessions and DDL Triggers and logs very comparable data. We know what object was changed, when it was done, and by whom. We also have access to the SQL query that was executed.

Data retention

If you log to the Windows Application Log or the Windows Security Log, you don’t have any possibilities to configure data retention. In case of logging to files, you can specify the number of files and their maximum sizes.

SQL Server Audit summary

  • Contains a lot of details
  • Info WHAT exactly was changed
  • Access to the executed SQL statement
  • Info WHEN object was changed
  • Info by WHO object was changed
  • Easy to set up
  • Logging to files or Windows Logs
  • Possibility to set additional filters
  • Easy viewing

  • No access to the old object definition

In the next post (yes – there will be another one), I will provide you with a general summary of all 5 ways to track database schema changes.


Share it:

Leave a Reply

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

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.