Utilizando o Trace Default do SQL Server
Neste artigo, vamos explorar a aplicação do trace padrão com um enfoque específico em eventos cruciais, tais como “Object:Created”, “Object:Altered” e “Object:Deleted”. Para otimizar essa análise, sugerimos a criação de um job dedicado à interpretação desses dados. Esse processo possibilita a identificação dos responsáveis por operações críticas, como a exclusão de uma tabela, além de determinar quando tais ações foram realizadas. O melhor aspecto é que essa análise pode ser conduzida sem a necessidade de ativar a auditoria, apenas realizando a leitura de informações já coletadas pelo trace padrão do SQL Server.
Para localizar o arquivo do trace padrão, utilize a consulta abaixo.
SELECT * FROM sys.traces WHERE is_default = 1
Com a query abaixo podemos obter quais são os 34 eventos que são coletados neste trace default. A query foi extraida do blog do Dirceu Resende .
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;
Neste caso irei criar um job com o foco em eventos específicos como “Object:Created”, “Object:Altered” e “Object:Deleted” para que seja rastreado.
O Job é composto de 3 steps sendo eles:
- Criação de um database com o nome de DBA
- Criação de uma tabela com o nome de LogAuditDefault
- Inserindo dados na tabela LogAuditDefault
O script utilizado para obter as informações foi encontrado no link SQLServerCentral – Identify who deleted your database or table , porém fiz uns ajuste para atender a minha necessidade e também o ajuste de qual arquivo “TRC” ser lido na hora da execução do job.
Fica ao seu critério configurar o schedule de execução do job, de acordo com a sua necessidade. Pois a cada execução do job ele irá acrescentar na tabela LogAuditDefault a diferença de dados que foram gerados desde da última execução do job.
No exemplo abaixo, é possível ver que o login “dev_jose” realizou uma operação de “Object:Deleted” no Object Name = Password, que possui o ObjectType = 8277-U, indicando que uma tabela foi deletada. Além disso, há também a operação de “Object:Created” no Object Name = v_erro, com o object type igual a 8278-V, indicando a criação de uma view.
A lista com todos os Object Type pode ser visualizada no site da documentação da Microsoft em ObjectType Trace Event Column .
Este job foi desenvolvido com o propósito de facilitar a identificação do login responsável por criar, alterar ou excluir objetos no banco de dados. É uma ferramenta valiosa para rastrear atividades cruciais dentro do sistema.
É crucial ressaltar a importância de realizar testes rigorosos em um ambiente de homologação antes de implementar o job em produção. Este procedimento garante que o job esteja funcionando conforme o esperado e que não haja interrupções inesperadas no ambiente de produção.
Segue abaixo o script completo do job.
USE [msdb]
GO
/****** Object: Job [Coletando Informações do Trace Default] Script Date: 29/09/2023 17:30:00 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Data Collector] Script Date: 29/09/2023 17:30:00 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Data Collector' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Data Collector'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Coletando Informações do Trace Default',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Job de coleta do trace default habilitado na instância SQL Server capturando os eventos de Create, Delete, Altered',
@category_name=N'Data Collector',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Criação Banco de dados DBA] Script Date: 29/09/2023 17:30:00 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Criação Banco de dados DBA',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'--Criação Banco de dados DBA
USE [master]
IF NOT EXISTS (SELECT name,* FROM sys.databases WHERE name = ''DBA'')
BEGIN
CREATE DATABASE [DBA]
END
GO',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Criação_da_Tabela_LogAuditDefault] Script Date: 29/09/2023 17:30:00 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Criação_da_Tabela_LogAuditDefault',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'--Criação Tabela LogAuditDefault
USE [DBA]
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N''[dbo].[LogAuditDefault]'') AND type in (N''U''))
BEGIN
use [DBA]
CREATE TABLE [LogAuditDefault]
( [ID] INT IDENTITY,
[name] NVARCHAR(128),
[ApplicationName] NVARCHAR(256),
[BigintData1] BIGINT,
[ClientProcessID] INT,
[ColumnPermissions] INT,
[DatabaseID] INT,
[DatabaseName] NVARCHAR(256),
[DBUserName] NVARCHAR(256),
[Duration] BIGINT,
[EndTime] DATETIME,
[Error] INT,
[EventSequence] BIGINT ,
[EventSubClass] NVARCHAR(139),
[FileName] NVARCHAR(256),
[HostName] NVARCHAR(256),
[IndexID] INT,
[IntegerData] INT,
[IsSystem] INT,
[LineNumber] INT,
[LoginName] NVARCHAR(256),
[LoginSid] IMAGE,
[NestLevel] INT,
[NTDomainName] NVARCHAR(256),
[NTUserName] NVARCHAR(256),
[ObjectID] INT,
[ObjectID2] BIGINT,
[ObjectName] NVARCHAR(256),
[ObjectType] NVARCHAR(139),
[OwnerName] NVARCHAR(256),
[ParentName] NVARCHAR(256),
[Permissions] BIGINT,
[RequestID] INT,
[RoleName] NVARCHAR(256),
[ServerName] NVARCHAR(256),
[SessionLoginName] NVARCHAR(256),
[Severity] INT,
[SPID] INT,
[StartTime] DATETIME,
[State] INT,
[Success] INT,
[TargetLoginName] NVARCHAR(256),
[TargetLoginSid] IMAGE,
[TargetUserName] NVARCHAR(256),
[TextData] NTEXT,
[TransactionID] BIGINT,
[Type] INT,
[XactSequence] BIGINT
);
END',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Inserindo_dados_LogAuditDefault] Script Date: 29/09/2023 17:30:00 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Inserindo_dados_LogAuditDefault',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'use [DBA]
DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1);
WITH cteObjectTypes AS
(
SELECT
TSV.trace_event_id,
TSV.subclass_name,
TSV.subclass_value
FROM
sys.trace_subclass_values AS TSV JOIN
sys.trace_columns AS TC ON
TSV.trace_column_id = TC.trace_column_id
WHERE
TC.[name] = ''ObjectType''
),
cteEventSubClasses AS
(
SELECT
TSV.trace_event_id,
TSV.subclass_name,
TSV.subclass_value
FROM
sys.trace_subclass_values AS TSV JOIN
sys.trace_columns AS TC ON
TSV.trace_column_id = TC.trace_column_id
WHERE
TC.[name] = ''EventSubClass''
)
INSERT INTO LogAuditDefault
SELECT
TE.[name],
I.ApplicationName,
I.BigintData1,
I.ClientProcessID,
I.ColumnPermissions,
I.DatabaseID,
I.DatabaseName,
I.DBUserName,
I.Duration,
I.EndTime,
I.Error,
I.EventSequence,
CONVERT(NVARCHAR(10), I.EventSubClass) + N''-'' + ESC.subclass_name AS EventSubClass,
I.FileName,
I.HostName,
I.IndexID,
I.IntegerData,
I.IsSystem,
I.LineNumber,
I.LoginName,
I.LoginSid,
I.NestLevel,
I.NTDomainName,
I.NTUserName,
I.ObjectID,
I.ObjectID2,
I.ObjectName,
CONVERT(NVARCHAR(10), I.ObjectType) + N''-'' + OT.subclass_name AS ObjectType,
I.OwnerName,
I.ParentName,
I.Permissions,
I.RequestID,
I.RoleName,
I.ServerName,
I.SessionLoginName,
I.Severity,
I.SPID,
I.StartTime,
I.State,
I.Success,
I.TargetLoginName,
I.TargetLoginSid,
I.TargetUserName,
I.TextData,
I.TransactionID,
I.Type,
I.XactSequence
FROM
sys.traces T CROSS APPLY
sys.fn_trace_gettable (@path, DEFAULT)
I JOIN
sys.trace_events AS TE ON
I.EventClass = TE.trace_event_id LEFT JOIN
cteEventSubClasses AS ESC ON
TE.trace_event_id = ESC.trace_event_id AND
I.EventSubClass = ESC.subclass_value LEFT JOIN
cteObjectTypes AS OT ON
TE.trace_event_id = OT.trace_event_id AND
I.ObjectType = OT.subclass_value
WHERE
T.is_default = 1 AND
TE.NAME IN (''Object:Deleted'', ''Object:Altered'', ''Object:Created'') AND
NOT EXISTS (
SELECT 1
FROM LogAuditDefault LD
WHERE LD.StartTime = I.StartTime
AND LD.EventSequence = I.EventSequence
)
and I.ApplicationName not in (''godapp'',''SQLServerCEIP'')
AND I.DatabaseName not in (''tempdb'',''DBA'')
ORDER BY I.StartTime DESC;',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Coleta_a_cada_30_minutos',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=30,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20230926,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'1c6aa8e7-fe08-4262-9a71-7fa3c64025c0'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO