Rastreando Objetos Criados ou Excluídos do seu Banco de dados SQL Server Sem Trilhas de Auditoria

Olá pessoal,

Tudo bem?

No post de hoje falaremos sobre um recurso bem interessante presente no SQL Server. Como rastrear objetos criados ou deletados do banco de dados sem trilhas de auditoria.

Nossa, isso é possível?

Sim, pessoal, claro que não é com a mesma precisão de uma trilha de auditoria, mas a boa noticia é que sim.. Esse recurso já quebra um galhão!!! ?

Em toda instalação o SQL Server disponibiliza um arquivo Trace por padrão já habilitado que pode ser consultado utilizando a função (fn_trace_gettable).

O mestre Dirceu Resende escreveu um artigo TOP do no qual ele demonstra diversos exemplos práticos de como utilizar este recurso e como é o funcionamento de toda essa estrutura.

https://www.dirceuresende.com/blog/utilizando-o-trace-padrao-do-sql-server-para-auditar-eventos-fn_trace_gettable/

Como fazer para consultar se o Trace padrão está habilitado na sua instância?

Utilize a query abaixo :

SELECT * FROM sys.traces WHERE is_default = 1

Laboratório :

Para exemplificar os testes e consultar os nossos objetos criados ou excluídos, vamos criar uma nova base e uma tabela qualquer.

CREATE	DATABASE TESTE_DEFAULT_TRACE
GO
USE TESTE_DEFAULT_TRACE
GO
CREATE TABLE TEST_TABLE_DEFAULT_TRACE(id INT, name VARCHAR(100))
GO

Com a base TESTE_DEFAULT_TRACE criada com sucesso no ambiente , agora vamos consultar na função fn_trace_gettable, qual foi o objeto criado.

DECLARE @path_default_trace VARCHAR(MAX);
SELECT  @path_default_trace = SUBSTRING(path, 1, LEN(path) - CHARINDEX('_', REVERSE(path))) + '.trc'
FROM sys.traces
WHERE is_default = 1;
--PRINT @path
SELECT DISTINCT e.name AS Name,
       t.DatabaseName,
       t.ObjectName AS Created_Object,
       t.LoginName AS 'Login',
       t.StartTime AS 'Dt_Evento',
       t.HostName 'Hostname',
       t.ServerName
FROM sys.fn_trace_gettable(@path_default_trace, 0) t
    INNER JOIN sys.trace_events e
        ON t.EventClass = e.trace_event_id
WHERE e.name = 'Object:Created'
      AND t.DatabaseID = DB_ID()
	  AND t.ObjectName NOT LIKE '_WA%'

Opa, legal hein!! ?

E agora se eu apagar essa tabela, tem como saber quem foi também?

Vamos dropar o objeto TEST_TABLE_DEFAULT_TRACE

DROP TABLE TEST_TABLE_DEFAULT_TRACE

Agora vamos rodar uma consulta semelhante, porém, desta vez filtrando os objetos excluídos :

DECLARE @path_default_trace VARCHAR(MAX);
SELECT  @path_default_trace = SUBSTRING(path, 1, LEN(path) - CHARINDEX('_', REVERSE(path))) + '.trc'
FROM sys.traces
WHERE is_default = 1;
--PRINT @path
SELECT DISTINCT e.name AS Name,
       t.DatabaseName,
       t.ObjectName AS Deleted_Object,
       t.LoginName AS 'Login',
       t.StartTime AS 'Dt_Evento',
       t.HostName 'Hostname',
       t.ServerName
FROM sys.fn_trace_gettable(@path_default_trace, 0) t
    INNER JOIN sys.trace_events e
        ON t.EventClass = e.trace_event_id
WHERE e.name = 'Object:Deleted'
      AND t.DatabaseID = DB_ID()

Muito legal, não é mesmo? ?

Seguem todos os eventos que podemos auditar com esse Trace padrão.

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

Créditos ao Dirceu por essa query mágica ? (Se estiver lendo isso, muito obrigado meu brother).

Por fim, é importante frisar que essa funcionalidade não substitui o uso de trilhas de auditoria, mais pra frente farei um artigo falando mais sobre esse recurso indispensável nos dias atuais. Também não sei dizer ao certo quanto tempo o SQL Server retém de informações nesse Trace padrão.

Mas de acordo com as informações da sys.traces na documentação oficial tudo indica que por padrão conseguimos reter 05 arquivos de até 20 MB com reciclagem (rollover) automática.

https://docs.microsoft.com/pt-br/sql/relational-databases/system-catalog-views/sys-traces-transact-sql?view=sql-server-ver16

E aí, conhecia esse recurso? Gostou da postagem??

Comenta aí, feedbacks são sempre bem-vindos!!!

Gustavo Larocca

Consultor SQL Server