How to create an Audit trigger to log object manipulation in SQL Server

Reading Time: 4 minutes

Hello people,
Good day.

Introduction

Today I come to bring you a very useful resource in the life of DBA's SQL Server, both for auditing alteration / creation / deletion of objects and for a “versioning” of SP's, Functions, etc., which is the use of triggers to log all DDL level changes to objects, including being able to record the query used for that. This is especially useful in the case of programming objects, such as Stored Procedures and Functions, where you can view each change made to these objects.

As an added point, I added a warning message for when someone creates an object in a system database (model, master and msdb). This alert can be incremented to send an email to the database team as well as visually notify the user who created the object, among others.

Implementation for a specific database

View source
For all users to have access to write to this table, you will need to execute the following commands:

Another note in this code is that this trigger writes data to some tables in the database, meaning that the user who changes any object in the database will need permissions to write the data to that table. For this reason, I added the grant command to the table for role public.

Another way to get around this is to use the EXECUTE AS clause 'login_com_permissao', so that the trigger will be executed with the permission of that EXECUTE AS user, but will record the data of the real user who is counting, avoiding the need to having to create all users in the database and release the permissions, looking like this:

Implementation for all databases

The solution presented above must be applied to each database of your server where you wish to log DDL operations. In the code snippet below, I will demonstrate how to apply this trigger to all server databases.

View source

As with database Trigger, for all users to have access to write to this table, you will need to execute the following commands:

Another note in this code is that this trigger writes data to some tables in the database, that is, the user who changes objects in the database will need permissions to write the data to that table, as well as having the user created in the database of that table. For this reason, I added the grant command to the table for role public.

Another way to get around this is to use the EXECUTE AS clause 'login_com_permissao', so that the trigger will be executed with the permission of that EXECUTE AS user, but will record the data of the real user who is counting, avoiding the need to having to create all users in the database and release the permissions, looking like this:

Results

Audit table created

Alert message when creating objects in system databases