SQL Server - Database and Instance Level Permission and Audit Audit Trigger (GRANT and REVOKE)

Views: 871
This post is the 8 part of 15 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.