Views: 4.829 views
- Audit on 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 audit and control of logins (Logon Trigger)
- Monitoring DDL and DCL Operations Using SQL Server fn_trace_gettable
- Using SQL Server Standard 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 to 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 more than 70 security items to validate your database.
- SQL Server - How to know the date of a user's last login
- SQL Server - How to Avoid and Protect Against Ransomware Attacks Like WannaCry on Your Database Server
- SQL Server – Beware of server role securityadmin! Using privilege elevation to become sysadmin
- SQL Server - How to Avoid SQL Injection? Stop using Dynamic Query as EXEC (@Query). Now.
- SQL Server - Understanding the Risks of TRUSTWORTHY Property Enabled in a Database
- SQL Server - Password Policies, Password Expiration, Mandatory Password Change, and Login Lockout after N attempts
Hello guys,
Good day.
Today I come to you, the solution of some questions that I asked myself in the environments in which I work, but I could not answer:
- Who created / changed / deleted a specific job?
- I accidentally deleted a Job .. How do I recreate it quickly, without needing to restore backup?
- Someone disabled a particular Job. Who was it?
- Someone changed the job description. I need to identify who it was.
To answer these questions, we will create a way to monitor the creation, modification, and deletion of Jobs in SQL Server by triggering the msdb.dbo.sysjobs table.
Creating the history table
Before creating the trigger that will retrieve the information, we will need to create the table that will store the information and create the history.
|
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) |
Trigger creation
Now that we create the table that will have our information, let's create the trigger that will populate it.
|
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 |
Querying the information
Now that our trigger is on, make some changes to your Jobs and see how the information is saved. It is possible to identify the date / time the event was triggered, the user who performed the event and even the query that was executed in the database, either for Job creation, deletion or modification.
See you!
Excellent helped me a lot. Thank you
Here is not recording anything, should I change something?
Perfect. Great post. Comes a lot of accurate information.