Hey guys,
Are you all right?

After a LONG time without posting any technical articles, I'm starting to have some time again to bring you more cool scripts and articles that I'd like to share with you.

In this post, I will demonstrate how to create a trigger to audit permission granting and removal events (GRANT and REVOKE) on objects, databases, user changes to roles (database roles and server roles), in addition to direct permissions on databases. This need arose through a demand that came to me, as some users were losing access to a certain instance and there was no control to identify who was removing and who granted this access.

Permissions history table creation script

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

USE [Auditoria]
GO

IF (OBJECT_ID('dbo.Trace_Alteracao_Privilegios') IS NULL)
BEGIN
    
    CREATE TABLE dbo.Trace_Alteracao_Privilegios (
        Id_Evento BIGINT IDENTITY(1, 1) PRIMARY KEY,
        Ds_Evento VARCHAR(255),
        Dt_Evento DATETIME,
        Ds_Database VARCHAR(255),
        Ds_Schema VARCHAR(255),
        Ds_Objeto VARCHAR(255),
        Ds_Tipo_Objeto VARCHAR(255),
        Ds_Usuario VARCHAR(255),
        Ds_Comando VARCHAR(MAX),
        Evento XML
    );

    GRANT INSERT ON dbo.Trace_Alteracao_Privilegios TO [public];

END

Trigger creation script

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

USE [master]
GO

IF ((SELECT COUNT(*) FROM sys.server_triggers WHERE name = 'trgAudit_Privileges') > 0) DROP TRIGGER [trgAudit_Privileges] ON ALL SERVER
GO

CREATE TRIGGER trgAudit_Privileges
ON ALL SERVER
FOR DDL_SERVER_SECURITY_EVENTS, DDL_DATABASE_SECURITY_EVENTS
AS
BEGIN
    
    
    DECLARE 
        @Ds_Evento NVARCHAR(255),
        @Ds_Schema NVARCHAR(255),
        @Ds_Database VARCHAR(255),
        @Ds_Objeto VARCHAR(255),
        @Ds_TipoObjeto VARCHAR(255),
        @Evento XML,
        @Ds_Comando VARCHAR(MAX);


    SELECT
        @Ds_Evento = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(max)'),
        @Ds_Schema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(max)'),
        @Ds_Objeto = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)'),
        @Ds_TipoObjeto = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(max)'),
        @Ds_Database = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(max)'),
        @Ds_Comando = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)'),
        @Evento = EVENTDATA()


    INSERT INTO Auditoria.dbo.Trace_Alteracao_Privilegios
    SELECT
        @Ds_Evento,
        GETDATE() AS Dt_Evento,
        @Ds_Database,
        @Ds_Schema,
        @Ds_Objeto,
        @Ds_TipoObjeto,
        SUSER_SNAME(),
        @Ds_Comando,
        @Evento


END

Results

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

That's it, folks!
A hug to you and see you in the next post.