Post Views 5,189 views
- 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,
Bom dia.
Hoje venho trazer pra vocês, a solução de algumas perguntas que eu me fazia nos ambientes em que trabalho, mas que eu não tinha como responder:
– Quem criou/alterou/excluiu um determinado job?
– Apaguei sem querer um Job.. Como faço pra recriá-lo rapidamente, sem precisar de restaurar backup ?
– Alguém desabilitou um determinado Job.. Quem foi ?
– Alguém alterou a descrição do Job.. Preciso identificar quem foi..
Para responder essas questões, iremos criar uma forma de monitorar a criação, modificação e exclusão de Jobs no SQL Server, através de uma trigger na tabela msdb.dbo.sysjobs.
Criando a tabela de histórico
Antes de criar a trigger que irá recuperar as informações, precisaremos criar a tabela que irá guardar as informações e criar o histórico.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE [dbo].[Job_Audit] ( [Id_Auditoria] [INT] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL, [Dt_Evento] [DATETIME] NULL DEFAULT (GETDATE()), [Ds_Usuario] [VARCHAR](50) NULL, [Ds_Job] [sysname] NULL, [Ds_Hostname] [VARCHAR](50) NULL, [Ds_Query] [VARCHAR](MAX) NULL, [Fl_Situacao] [TINYINT] NULL ) WITH (DATA_COMPRESSION=PAGE) |
Criação da trigger
Agora que criamos a tabela que terá as nossas informações, vamos criar a trigger que irá populá-la.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 |
USE [msdb] GO /*************************************************************************************************** -- Trigger para os Jobs ***************************************************************************************************/ IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'trgJobs_Status') > 0) DROP TRIGGER dbo.trgJobs_Status GO CREATE TRIGGER trgJobs_Status ON sysjobs AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON DECLARE @UserName VARCHAR(50) = SYSTEM_USER, @HostName VARCHAR(50) = HOST_NAME(), @JobName sysname, @New_Enabled INT, @Old_Enabled INT, @ExecStr VARCHAR(100), @Qry VARCHAR(MAX) SELECT @New_Enabled = [enabled] FROM Inserted SELECT @Old_Enabled = [enabled] FROM Deleted SELECT @JobName = [name] FROM Deleted IF (@JobName IS NULL) SELECT @JobName = [name] FROM Deleted -- Identificando a query executada CREATE TABLE #inputbuffer ( [EventType] NVARCHAR(60), [Parameters] INT, [EventInfo] VARCHAR(MAX) ) SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')' INSERT INTO #inputbuffer EXEC (@ExecStr) SET @Qry = (SELECT EventInfo FROM #inputbuffer) -- Verifica se houve alteração de status IF (@New_Enabled != @Old_Enabled) BEGIN IF (@New_Enabled = 1) BEGIN INSERT INTO dbo.Job_Audit ( Ds_Usuario, Ds_Job, Ds_Hostname, Ds_Query, Fl_Situacao ) SELECT @UserName, @JobName, @HostName, @Qry, 1 END IF (@New_Enabled = 0) BEGIN INSERT INTO dbo.Job_Audit ( Ds_Usuario, Ds_Job, Ds_Hostname, Ds_Query, Fl_Situacao ) SELECT @UserName, @JobName, @HostName, @Qry, 0 END END ELSE BEGIN INSERT INTO dbo.Job_Audit ( Ds_Usuario, Ds_Job, Ds_Hostname, Ds_Query ) SELECT @UserName, @JobName, @HostName, @Qry END END GO /*************************************************************************************************** -- Trigger para os Schedules dos Jobs ***************************************************************************************************/ IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'trgAudit_Schedules') > 0) DROP TRIGGER dbo.trgAudit_Schedules GO CREATE TRIGGER [dbo].[trgAudit_Schedules] ON [dbo].[sysschedules] AFTER UPDATE, DELETE AS BEGIN SET NOCOUNT ON DECLARE @UserName VARCHAR(50) = SYSTEM_USER, @HostName VARCHAR(50) = HOST_NAME(), @JobName VARCHAR(MAX) = '', @ExecStr VARCHAR(100), @Qry VARCHAR(MAX) IF ((SELECT COUNT(*) FROM Inserted) > 0) BEGIN SELECT @JobName += (CASE WHEN @JobName != '' THEN ' | ' ELSE '' END) + A.[name] FROM msdb.dbo.sysjobs A JOIN msdb.dbo.sysjobschedules B ON A.job_id = B.job_id JOIN Inserted C ON B.schedule_id = C.schedule_id END ELSE BEGIN SELECT @JobName += (CASE WHEN @JobName != '' THEN ' | ' ELSE '' END) + A.[name] FROM msdb.dbo.sysjobs A JOIN msdb.dbo.sysjobschedules B ON A.job_id = B.job_id JOIN Deleted C ON B.schedule_id = C.schedule_id END -- Identificando a query executada CREATE TABLE #inputbuffer ( [EventType] NVARCHAR(60), [Parameters] INT, [EventInfo] VARCHAR(MAX) ) SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')' INSERT INTO #inputbuffer EXEC (@ExecStr) SET @Qry = (SELECT EventInfo FROM #inputbuffer) IF (@JobName != '') BEGIN INSERT INTO dbo.Job_Audit ( Ds_Usuario, Ds_Job, Ds_Hostname, Ds_Query ) SELECT @UserName, @JobName, @HostName, @Qry END END GO ALTER TABLE [dbo].[sysschedules] ENABLE TRIGGER [trgAudit_Schedules] GO /*************************************************************************************************** -- Trigger para os Schedules ***************************************************************************************************/ IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'trgAudit_Jobs_Schedules') > 0) DROP TRIGGER dbo.trgAudit_Jobs_Schedules GO CREATE TRIGGER [dbo].[trgAudit_Jobs_Schedules] ON [dbo].[sysjobschedules] AFTER INSERT AS BEGIN SET NOCOUNT ON DECLARE @UserName VARCHAR(50) = SYSTEM_USER, @HostName VARCHAR(50) = HOST_NAME(), @JobName sysname, @ExecStr VARCHAR(100), @Qry VARCHAR(MAX) IF ((SELECT COUNT(*) FROM Inserted) > 0) BEGIN SELECT @JobName = A.[name] FROM msdb.dbo.sysjobs A JOIN Inserted B ON A.job_id = B.job_id END ELSE BEGIN SELECT @JobName = A.[name] FROM msdb.dbo.sysjobs A JOIN Deleted B ON A.job_id = B.job_id END -- Identificando a query executada CREATE TABLE #inputbuffer ( [EventType] NVARCHAR(60), [Parameters] INT, [EventInfo] VARCHAR(MAX) ) SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')' INSERT INTO #inputbuffer EXEC (@ExecStr) SET @Qry = (SELECT EventInfo FROM #inputbuffer) INSERT INTO dbo.Job_Audit ( Ds_Usuario, Ds_Job, Ds_Hostname, Ds_Query ) SELECT @UserName, @JobName, @HostName, @Qry END GO ALTER TABLE [dbo].[sysjobschedules] ENABLE TRIGGER [trgAudit_Jobs_Schedules] GO |
Consultando as informações
Agora que nossa trigger está ativada, faça algumas alterações em seus Jobs e veja como as informações são gravadas. É possível identificar a data/hora que o evento foi acionado, o usuário que realizou o evento e mesmo a query que foi executada no banco, seja para criação, exclusão ou alteração do Job.
Até mais!
![SQL Server – The activated proc ‘[dbo].[sp_syspolicy_events_reader]’ running on queue ‘msdb.dbo.syspolicy_event_queue’](http://dirceuresende.com/wp-content/themes/boxcard/img/thumb-medium.png)
Portuguese 
English
Excelente me ajudou muito . Obrigado
Aqui não está gravando nada, devo alterar algo?
Perfeito. Ótimo Post. Vem muitas informacoes precisas.