Click on the banner to learn about and purchase my database training on Azure

SQL Server - Permissions and privileges audit trigger at database and instance level (GRANT and REVOKE)

Views: 1.812 views
This post is the 7 part of 20 in the series. Security and Audit
Reading Time: 2 minutes

Hello people,
All right with you ?

After A LOT of time without posting any technical articles, I am starting to take time again to bring in more cool scripts and articles that I would like to share with you.

In this post, I will demonstrate how to create a trigger to audit grant and remove events (GRANT and REVOKE) on objects, databases, user role changes (database roles and server roles), and direct permissions on databases. This need came through a demand that came to me because some users were losing access in a particular instance and had no control to identify who was removing and who granted those accesses.

Permissions History Table Creation Script

With the script below, you can create the table that will store all the history of permissions granted and removed in the instance.

Trigger creation script

With the script below, you will create the trigger that collects the permissions audit.


Now I will demonstrate some examples of what this trigger can audit.

That's it folks!
A hug to you and until the next post.