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

Views: 2.879
This post is the 4 part of 15 in the series. Security and Audit
Reading Time: 4 minutes

Hello people,
Good day.

Introduction

Today I bring to you a very useful feature in the life of SQL Server DBA, both for auditing change / creation / deletion of objects and for "versioning" SP's, Functions, etc., which is the use of triggers to log all DDL level changes to objects, including being able to write the query used for it. This is especially useful for programming objects such as Stored Procedures and Functions, where you can view each change you make 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 around this is to use the EXECUTE AS clause 'login_com_permission', so that the trigger will be executed with the permission of that EXECUTE AS user, but will record the actual user data being counted, avoiding the need to have to create all users in the database and release permissions, 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 around this is to use the EXECUTE AS clause 'login_com_permission', so that the trigger will be executed with the permission of that EXECUTE AS user, but will record the actual user data being counted, avoiding the need to have to create all users in the database and release permissions, like this:

Results

Audit table created
trgAlteracao_Objects_Final

Alert message when creating objects in system databases
trgAlteracao_Object2