Monitorando operações de DDL e DCL utilizando a fn_trace_gettable do SQL Server

Olá pessoal,
Boa tarde!

Neste post vou demonstrar como identificar os eventos de DDL e DCL que ocorrem na instância SQL Server. Isso nos permite identificar qual o usuário que criou/alterou/apagou um objeto, ou quem adicionou/removeu um usuário em uma role de sistema ou role de database e várias outras coisas sem a necessidade de criar nenhum controle ou monitoramento interno, utilizando apenas o trace padrão do SQL Server, onde iremos consultar seus dados através da função fn_trace_gettable.

Há um tempo atrás eu publiquei aqui o post Como criar uma trigger de Auditoria para logar a manipulação de objetos no SQL Server que permitia criar uma server trigger que realiza essa auditoria. Apesar da função fn_trace_gettable retornar boa parte das informações disponíveis nesse controle interno, essa trigger permite fazer o versionamento dos objetos alterados, como procedures, functions, tabelas, etc. Ou seja, caso você queira automatizar o versionamento de código-fonte de objetos no banco de dados, o post mais indicado é o citado logo acima.

Relembrando os conceitos de DDL e DCL

Acredito que esses conceitos já são bem conhecidos pela maioria de vocês, mas vou reforçar o que é cada coisa para não restarem dúvidas:

Operações DDL (Data Definition Language)
Sâo utilizados para definir ou alterar estruturas ou schemas de bancos de dados

CREATE – Utilizado para criar objetos no banco de dados
ALTER – Altera a estrutura de um objeto
DROP – Remove um objeto do banco de dados
TRUNCATE – Apapga todos os registros de uma tabela, incluindo o espaço alocado.
COMMENT – Adiciona comentários em objetos
RENAME – Renomeia um objeto

Operações DCL (Data Control Language)
Sâo utilizados para gerenciamento de permissões e privilégios de usuários em determinados objetos da instância SQL Server

GRANT – Concede acesso para um usuário em um determinado objeto
REVOKE – Remove o acesso de um usuário em um determinado objeto

Identificando os tipos de eventos do trace

Utilizando a query abaixo, podemos identificar rapidamente todos os EventClass que podemos utilizar na função ::fn_trace_gettable:

DECLARE @id INT = ( SELECT id FROM sys.traces WHERE is_default = 1 )
 
SELECT DISTINCT
    eventid,
    name
FROM
    fn_trace_geteventinfo(@id) A
    JOIN sys.trace_events B ON A.eventid = B.trace_event_id

Identificando os eventos de DDL e DCL na instância SQL Server

Com a query abaixo, é possível identificar quem foi a última pessoa a alterar um determinado objeto na instância (Ex: Stored Procedure), quem adicionou um determinado usuário em uma role, quem apagou uma tabela, quem executou um comando de TRUNCATE, etc, e tudo isso sem precisar criar nenhum controle além dos já disponíveis e ativados por padrão no SQL Server.

DECLARE @Ds_Arquivo_Trace VARCHAR(255) = (SELECT SUBSTRING([path], 0, LEN([path])-CHARINDEX('\', REVERSE([path]))+1) + '\Log.trc' FROM sys.traces WHERE is_default = 1)

SELECT
    A.HostName,
    A.ApplicationName,
    A.NTUserName,
    A.NTDomainName,
    A.LoginName,
    A.SPID,
    A.EventClass,
    B.name,
    A.EventSubClass,
    A.TextData,
    A.StartTime,
    A.ObjectName,
    A.DatabaseName,
    A.TargetLoginName,
    A.TargetUserName
FROM
    [fn_trace_gettable](@Ds_Arquivo_Trace, DEFAULT) A
    JOIN master.sys.trace_events B ON A.EventClass = B.trace_event_id
WHERE
    A.EventClass IN ( 164, 46, 47, 108, 110, 152 ) 
    AND A.StartTime >= GETDATE()-7
    AND A.LoginName NOT IN ( 'NT AUTHORITY\NETWORK SERVICE' )
    AND A.LoginName NOT LIKE '%SQLTELEMETRY$%'
    AND A.DatabaseName != 'tempdb'
    AND NOT (B.name LIKE 'Object:%' AND A.ObjectName IS NULL )
    AND NOT (A.ApplicationName LIKE 'Red Gate%' OR A.ApplicationName LIKE '%Intellisense%' OR A.ApplicationName = 'DacFx Deploy')
ORDER BY
    StartTime DESC

É isso aí, pessoal!
Obrigado pela visita e até o próximo post.