- Auditoria no SQL Server (Server Audit)
- Como criar uma auditoria para monitorar a criação, modificação e exclusão de Jobs no SQL Server
- Como criar uma trigger de Auditoria para logar a manipulação de objetos no SQL Server
- SQL Server – Como implementar auditoria e controle de logins (Trigger de Logon)
- Monitorando operações de DDL e DCL utilizando a fn_trace_gettable do SQL Server
- Utilizando o trace padrão do SQL Server para auditar eventos (fn_trace_gettable)
- SQL Server – Trigger de auditoria de permissões e privilégios a nível de database e instância (GRANT e REVOKE)
- SQL Server – Como monitorar e auditar alterações de dados em tabelas utilizando Change Data Capture (CDC)
- SQL Server 2016 – Como “viajar no tempo” utilizando o recurso Temporal Tables
- SQL Server – Como utilizar auditoria para mapear permissões necessárias reais em um usuário
- SQL Server – Trigger para prevenir e impedir alterações em tabelas
- SQL Server – Como criar um histórico de alterações de dados para suas tabelas (logs para auditoria)
- SQL Server – Como evitar ataques de força bruta no seu banco de dados
- SQL Server – Checklist de Segurança – Uma SP com mais de 70 itens de segurança para validar seu banco de dados
- SQL Server – Como saber a data do último login de um usuário
- SQL Server – Como evitar e se proteger de ataques de Ransomware, como WannaCry, no seu servidor de banco de dados
- SQL Server – Cuidado com a server role securityadmin! Utilizando elevação de privilégios para virar sysadmin
- SQL Server – Como evitar SQL Injection? Pare de utilizar Query Dinâmica como EXEC(@Query). Agora.
- SQL Server – Entendendo os riscos da propriedade TRUSTWORTHY habilitada em um database
- SQL Server – Políticas de Senhas, Expiração de Senha, Troca de Senha Obrigatória e Bloqueio de Login após N tentativas
- SQL Server – Como criar uma auditoria de logins utilizando os logs da instância
Olá pessoal,
Boa tarde!
Neste post, vou demonstrar como se obter informações importantes sobre vários eventos que ocorrem na instância SQL Server sem precisar ativar nenhum controle ou auditoria, apenas lendo as informações já coletadas pelo trace padrão do SQL Server.
Embora eu já tenha falado sobre isso em alguns outros posts, resolvi fechar esse assunto e fazer um post com mais vários exemplos de utilização.
O trace padrão do SQL coleta 34 tipos diferentes de eventos, que podem ser gerados manualmente por ferramentas como o SQL Server Profiler ou com queries Transact-SQL.
Os exemplos abaixo mostram como pegar informações de diferentes tipos de eventos, como Shrink, AutoGrow, Atualização de Estatísticas Automática, Backup/Restore, DBCC, Ausência de Estatísticas de Colunas, dentro outros.
Um importante ponto que se deve observar, é que esse trace padrão do SQL Server é executado em background por padrão, coletando apenas alguns eventos e por isso, gera um impacto mínimo na instância SQL Server.
Ao ativar um trace utilizando o SQL Server Profiler, que exibe os resultados em tempo real pela interface, você pode acabar gerando impactos como degradação de performance no ambiente, principalmente se não filtrar muito bem apenas os eventos que deseja observar e quais critérios serão definidos para identificar as sessões que farão parte do Trace.
Trace padrão do SQL Server
Normalmente, temos executando em nossa instância SQL Server apenas o trace padrão, que é habilitado por padrão, embora em alguns ambientes você possa encontrar mais de um trace executando, caso algum DBA crie um trace personalizado.
Listando os traces ativos na instância
Para listar os traces ativos na instância, você pode utilizar a query abaixo:
|
1 |
SELECT * FROM sys.traces |
Identificando o trace padrão
Embora o trace padrão geralmente seja o trace com o ID = 1, nem sempre isso ocorre. Por isso, recomendo utilizar a query abaixo para identificar o trace padrão:
|
1 |
SELECT * FROM sys.traces WHERE is_default = 1 |
Listando os eventos do trace padrão
Como citado acima, o trace padrão do SQL Server coleta 34 eventos diferentes. Para identificar a lista completa de eventos, basta executar essa query:
|
1 2 3 4 5 6 7 8 |
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 |
Ativando o Trace Padrão (Já vem habilitado após a instalação)
Para ativar o trace padrão do SQL Server é bem simples, basta executar a sp_configure:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'default trace enabled', 1; GO RECONFIGURE; GO EXEC sp_configure 'show advanced options', 0; GO RECONFIGURE; GO |
Desativando o Trace Padrão
Para desativar o trace padrão do SQL Server é tão simples quanto ativar. Basta executar a sp_configure:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'default trace enabled', 0; GO RECONFIGURE; GO EXEC sp_configure 'show advanced options', 0; GO RECONFIGURE; GO |
Identificando os eventos
Uma forma simples de identificar os eventos que ocorreram na instância é executando a query abaixo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT B.trace_event_id, B.name AS EventName, A.DatabaseName, A.ApplicationName, A.LoginName, COUNT(*) AS Quantity FROM sys.fn_trace_gettable(@path, DEFAULT) A INNER JOIN sys.trace_events B ON A.EventClass = B.trace_event_id GROUP BY B.trace_event_id, B.name, A.DatabaseName, A.ApplicationName, A.LoginName ORDER BY B.name, A.DatabaseName, A.ApplicationName, A.LoginName |
Identificando eventos de Autogrowth
Um dos eventos mais importantes que podemos observar com o trace padrão, são os eventos de Autogrowth, que ocorrem quando essa opção está habilitada no database e ele atinge o tamanho máximo, necessitando de alocar mais espaço no disco.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT DatabaseName, [FileName], CASE EventClass WHEN 92 THEN 'Data File Auto Grow' WHEN 93 THEN 'Log File Auto Grow' END AS EventClass, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName FROM sys.fn_trace_gettable(@path, DEFAULT) WHERE EventClass IN ( 92, 93 ) ORDER BY StartTime DESC |
Identificando eventos de Shrink de Disco
Outro evento muito legal de ser monitorar, é o evento que Shrink de disco, que ocorre quando o database está alocando mais espaço que está utilizando e o DBA realoca o espaço alocado, resultando em espaço livre no filesystem.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName FROM sys.fn_trace_gettable(@path, DEFAULT) WHERE EventClass IN ( 116 ) AND TextData LIKE 'DBCC%SHRINK%' ORDER BY StartTime DESC |
Identificando quando comandos DBCC foram executados na instância
Auditoria bem útil para identificar quem executou ou quando um comando DBCC foi executado na instância
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName FROM sys.fn_trace_gettable(@path, DEFAULT) WHERE EventClass IN ( 116 ) ORDER BY StartTime DESC |
Identificando quando os backups foram realizados
Outra auditoria bem interessante para o DBA, que possibilita identificar a data e quem rodou os backups na instância (que também pode ser consultado na tabela msdb.dbo.backupset).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT DatabaseName, TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName FROM sys.fn_trace_gettable(@path, DEFAULT) WHERE EventClass IN ( 115 ) AND EventSubClass = 1 ORDER BY StartTime DESC |
Identificando quando os backups foram restaurados
Auditoria bem interessante que permite identificar a data e qual usuário realizou um restore na instância (que também pode ser consultando nas tabelas msdb.dbo.restorehistory, msdb.dbo.restorefile e msdb.dbo.restorefilegroup)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName FROM sys.fn_trace_gettable(@path, DEFAULT) WHERE EventClass IN ( 115 ) AND EventSubClass = 2 ORDER BY StartTime DESC |
Esses são só alguns exemplos do que o trace padrão nos retorna. Existe a extensa lista de eventos pra vocês explorarem!
Boa sorte, abraço e até o próximo post!
Portuguese 
English
Bom dia Dirceu! estou fazendo um teste, gravando o trace direto da ferramenta Profiler, e também gravando o trace do arquivo para a tabela usando o fn_trace_gettable. Usando a função, não estou conseguindo gravar o Rownumber. Sabe me dizer como gravar esse campo? percebi que há outros campos que a função não grava, mas estes não são importantes para mim, no momento.
Obrigado
Cássio, não me lembro de ter visto isso.. Você já conseguiu resolver isso?
Se não, me fala que eu tento simular esse cenário e te falo
Dirceu, parabéns pelo post. Me ajudou muito e abriu minha visão para as possibilidades de auditoria no BD.