Olá pessoal,
Tudo bem com vocês ?

Depois de MUITO tempo sem postar nenhum artigo técnico, estou começando a ter um tempo novamente para trazer mais scripts e artigos legais que eu gostaria de compartilhar com vocês.

Neste post, vou demonstrar como criar uma trigger para auditar eventos de concessão e remoção de permissões (GRANT e REVOKE) em objetos, databases, alterações de usuários em roles (database roles e server roles), além de permissões diretas em databases. Essa necessidade surgiu através de uma demanda que chegou pra mim, pois alguns usuários estavam perdendo acesso em uma determinada instância e não tinha nenhum controle para identificar quem estava removendo e quem concedeu esses acessos.

Script de criação da tabela de histórico de permissões

Com o script abaixo, você poderá criar a tabela que vai armazenar todo o histórico de permissões concedidas e removidas na instância.

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

Script de criação da trigger

Com o script abaixo, você irá criar a trigger que faz a coleta a auditoria das permissões.

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

Resultados

Agora vou demonstrar alguns exemplos do que essa trigger pode auditar.

É isso aí, pessoal!
Um abraço pra vocês e até o próximo post.