- Auditing in SQL Server (Server Audit)
- How to Create an Audit to Monitor Job Creation, Modification, and Deletion in SQL Server
- How to create an Audit trigger to log object manipulation in SQL Server
- SQL Server - How to implement login auditing and control (Logon Trigger)
- Monitoring DDL and DCL operations using SQL Server's fn_trace_gettable
- Using the standard SQL Server trace to audit events (fn_trace_gettable)
- SQL Server – Permissions and privileges audit trigger at database and instance level (GRANT and REVOKE)
- SQL Server - How to monitor and audit data changes in tables using Change Data Capture (CDC)
- SQL Server 2016 - How to "time travel" using the Temporal Tables feature
- SQL Server - How to use auditing to map actual required permissions on a user
- SQL Server - Trigger to prevent and prevent changes in tables
- SQL Server - How to Create a Data Change History for Your Tables (Audit Logs)
- SQL Server - How to avoid brute force attacks on your database
- SQL Server – Security Checklist – An SP with over 70 security items to validate your database
- SQL Server - How to know the last login date of a user
- SQL Server - How to avoid and protect yourself from Ransomware attacks like WannaCry on your database server
- SQL Server - Watch out for the securityadmin server role! Using elevation of privileges to become sysadmin
- SQL Server – How to avoid SQL Injection? Stop using Dynamic Query like EXEC(@Query). Now.
- SQL Server - Understanding the risks of the TRUSTWORTHY property enabled on a database
- SQL Server - Password Policies, Password Expiration, Mandatory Password Change and Login Blocking after several Attempts
- SQL Server - How to create a login audit using instance logs
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.