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

Tempo de leitura: 3 minutos

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

1	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

CREATE DATABASE TESTE_DEFAULT_TRACE

USE TESTE_DEFAULT_TRACE

CREATE TABLE TEST_TABLE_DEFAULT_TRACE(id INT, name VARCHAR(100))

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%'

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

1	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()

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

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

Nm_Recurso	Descrição
Max_size	Limite máximo do tamanho do arquivo de rastreamento em megabytes (MB). Este valor será nulo quando o rastreamento for referente a um conjunto de linhas.
Status	Status do rastreamento: 1 – ativo , 0 – inativo.
Max_files	Número máximo de arquivos de substituição. Este valor será nulo se o número máximo não for definido.
Is_default	1 = rastreamento padrão.
Is_rollover	1 = a opção de substituição está habilitada.

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

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

Gustavo Larocca

Consultor SQL Server

audit default trace microsoft security sql server

4 comentários

Anderson Aroucha disse:

31 de maio de 2022 às 10:39

Muito bom!
Alternativa marota essa aí!!

Responder
- Gustavo disse:
  
  31 de maio de 2022 às 11:38
  
  Opa!! que bom que gostou mano!! Tmj meu parceiro!!
  
  Responder
Carlos Souza disse:

27 de outubro de 2022 às 20:46

Muito bom. Simples e muito funcional.

Responder
Marcelo Aloysio disse:

23 de novembro de 2023 às 17:54

Fantástico! Queria saber como ter o mesmo recurso na pesquisa pro procedures e functions.

Responder

Deixe uma respostaCancelar resposta

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

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