Using Triggers for auditing changes to sql jobs

Recently there was a job that was changed to stop at a step earlier than normal (presumably for testing).  When it ran that night, it didn't run all the steps.  This job has a lot of dependencies which needed to be re-run the next day and caused a bit of chaos for a few applications, data warehouse data, and reports.

In order to see who made the change, there would have to be some sort of auditing running prior to when the change happened.  Auditing of sql jobs and more had already been identified as a project for our department, but hasn't been prioritized yet.  This of course, doesn't help identify what happened for this specific time.  :)

 In the mean-time, management wants to be able to track these types of changes.  Therefore, I started researching the multiple options and found some common solutions:

• SQL Audit - This logs specific events to either a file, the Windows Security event log, or the Windows Application event log.  While this is a great way of collecting information, it isn't something that is easily queryable.  At my company, they also have recently implemented an IPSec logging tool.  We will be rolling SQL Server logging into it, but while they are still working through other logging, I don't want to add even more to their plate.  When they have a good handle on things, I will be looking into the reporting capabilities to see if changes will be easily reportable.
• Extended Events - I know there are a lot of capabilities in extended events to handle auditing.  At this time, I am not as familiar with extended events to be able to ensure performance wont be impacted.  I know that the data can be stored in an easily queryable way and has the ability to correlate the data between SQL and Windows.  This is something I will be looking into much more when the project is prioritized.
• Change Data Capture - This is a great way of identifying the data that changes in a table.  There are some considerations to be thought of, mostly making sure the structures stay in sync when changing the underlying table.  I have used it before for user tables, but never for system tables. 

As part of the larger project, I will be wanting to audit more than just sql job changes, such as configuration changes, linked server changes, ssis package changes, and of course security and permission changes, etc..  For now, I am looking at turning something on fairly quickly to solve the immediate need.  Then, take time with the project to research the best overall method to be consistent across all database servers for simplicity, and best for routine monitoring.  After all, just because you log everything, doesn't mean its useful unless the data is reviewed for potential vulnerabilities or threats.

I decided to do something fairly simple.  I will be placing triggers on the sysjobs, sysjobsteps and sysjobschedules tables to record all changes into an auditing table.  I am able to get this running in a short timeframe without having to worry too much about performance.  The auditing will only happen when a job is changed, and it will be easily queryable. 

I found a few posts online that were helpful, but the one that helped me decide on how to record the changes in a simple structure was at craftdba.com in the post: Sep 2011 (SNESSUG) / Mar 2013 (SAT 184) / Apr 2013 (SAT 203) / Aug 2013 (SAT 235)

You can download the code by clicking here 

Step 1

First, lets make the audit table which will store all of the changes.

IF OBJECT_ID('[dbo].[AuditJobChanges]') IS NOT NULL 
  DROP TABLE [dbo].[AuditJobChanges]
;

-- Add the table
CREATE TABLE [dbo].[AuditJobChanges]
(
  [AuditChangeID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
  [AuditChangeDate] [datetime] NOT NULL,
  [AuditChangeType] [varchar](20) NOT NULL,
  [AuditChangePerformedBy] [nvarchar](256) NOT NULL,
  [ApplicationName] [nvarchar](128) NOT NULL,
  [HostName] [nvarchar](128) NOT NULL,
  [SchemaName] [sysname] NOT NULL,
  [ObjectName] [sysname] NOT NULL,
  [XML_RECSET] [xml] NULL,
 CONSTRAINT [PK_AJC_AuditChangeID] PRIMARY KEY CLUSTERED ([AuditChangeID] ASC)
) ON [PRIMARY]
;
-- Add defaults for key information
ALTER TABLE [dbo].[AuditJobChanges] ADD CONSTRAINT [DF_AJC_AuditChangeDate] DEFAULT (getdate()) FOR [AuditChangeDate];
ALTER TABLE [dbo].[AuditJobChanges] ADD CONSTRAINT [DF_AJC_AuditChangeType] DEFAULT ('?') FOR [AuditChangeType];
ALTER TABLE [dbo].[AuditJobChanges] ADD CONSTRAINT [DF_AJC_AuditChangePerformedBy] DEFAULT (coalesce(suser_sname(),'?')) FOR [AuditChangePerformedBy];
ALTER TABLE [dbo].[AuditJobChanges] ADD CONSTRAINT [DF_AJC_ApplicationName] DEFAULT (coalesce(app_name(),'?')) FOR [ApplicationName];
ALTER TABLE [dbo].[AuditJobChanges] ADD CONSTRAINT [DF_AJC_HostName] DEFAULT (coalesce(host_name(),'?')) FOR [HostName];

Step 2

Next, lets set our baseline and insert the state the jobs are in now.  Each insert grabs the info from each of the tables we will be adding a trigger to.  This way when we are comparing the first edit that happens once the triggers are in place, we have something to compare it to.

declare @lv_userName varchar(25)
set @lv_userName = 'DOMAIN\USER';

insert into dbo.AuditJobChanges (AuditChangeDate, AuditChangeType, AuditChangePerformedBy, ApplicationName, HostName, SchemaName, ObjectName, XML_RECSET)
 select getdate() as AuditChangeDate
 , 'ORIG' as AuditChangeType
 , @lv_userName as AuditChangePerformedBy
 , 'Microsoft SQL Server Management Studio' as ApplicationName
 , @@SERVERNAME as HostName
 , 'dbo' as SchemaName
 , 'sysjobs' as ObjectName
 --, name
 , XML_RECSET = (select * from dbo.sysjobs as j2 where j2.job_id = j1.job_id for xml path('RecordSet'), TYPE)
 from dbo.sysjobs as j1;



insert into dbo.AuditJobChanges (AuditChangeDate, AuditChangeType, AuditChangePerformedBy, ApplicationName, HostName, SchemaName, ObjectName, XML_RECSET)
 select getdate() as AuditChangeDate
 , 'ORIG' as AuditChangeType
 , @lv_userName as AuditChangePerformedBy
 , 'Microsoft SQL Server Management Studio' as ApplicationName
 , @@SERVERNAME as HostName
 , 'dbo' as SchemaName
 , 'sysjobsteps' as ObjectName
 --, name
 , XML_RECSET = (select * from dbo.sysjobsteps as j2 where j2.job_id = j1.job_id for xml path('RecordSet'), TYPE)
 from dbo.sysjobsteps as j1;



insert into dbo.AuditJobChanges (AuditChangeDate, AuditChangeType, AuditChangePerformedBy, ApplicationName, HostName, SchemaName, ObjectName, XML_RECSET)
 select getdate() as AuditChangeDate
 , 'ORIG' as AuditChangeType
 , @lv_userName as AuditChangePerformedBy
 , 'Microsoft SQL Server Management Studio' as ApplicationName
 , @@SERVERNAME as HostName
 , 'dbo' as SchemaName
 , 'sysjobschedules' as ObjectName
 --, name
 , XML_RECSET = (select * from dbo.sysjobschedules as j2 where j2.job_id = j1.job_id for xml path('RecordSet'), TYPE)
 from dbo.sysjobschedules as j1;

Step 3

Now we are ready to add the triggers.  I chose to add a new trigger to each table.  I know the sysjobs table already has a trigger, but since this is a system table, I chose not to change the existing trigger.  

if object_id('[dbo].[trig_sysjobs_insert_update_delete]') is not null
	drop trigger [dbo].[trig_sysjobs_insert_update_delete]
go

CREATE TRIGGER [dbo].[trig_sysjobs_insert_update_delete]
ON [dbo].[sysjobs]
FOR INSERT, UPDATE, DELETE
AS

--************************************************************************
--
-- Vers Who       Date       What
-- ---- --------- ---------- -------------------------
-- 1.0  ggable    09/21/2017 Initial Release
-- 1.1  ggable    09/28/2017 added condition not to insert when change comes from system automated update ('SQLAgent - Job Manager')
--************************************************************************



BEGIN

declare @lv_schemaName varchar(25) = 'dbo'
, @lv_tablename varchar(50) = 'sysjobs'



declare @lv_userName nvarchar(50) = user_name()
, @lv_sUserSName nvarchar(50) = suser_sname()
, @lv_originalLogin nvarchar(50) = original_login()
, @lv_applicationName nvarchar(50) = app_name()
, @lv_hostName nvarchar(50) = host_name()
, @lb_IncludeSystemAutomedUpdates bit = 0

select @lv_userName = user_name()
select @lv_sUserSName = suser_sname()
select @lv_originalLogin = original_login()
select @lv_applicationName = app_name()
select @lv_hostName = host_name()



IF @lv_applicationName <> 'SQLAgent - Job Manager' OR @lb_IncludeSystemAutomedUpdates = 1 BEGIN

	-- Detect inserts
	IF EXISTS (select * from inserted) AND NOT EXISTS (select * from deleted)
	BEGIN

		insert into dbo.AuditJobChanges (AuditChangeDate
										, AuditChangeType
										, AuditChangePerformedBy
										, ApplicationName
										, HostName
										, SchemaName
										, ObjectName
										, XML_RECSET
										)
			select getdate()
			, 'INSERT'
			, @lv_sUserSName
			, @lv_applicationName
			, @lv_hostName
			, @lv_schemaName
			, @lv_tablename
			, XML_RECSET = (select * from inserted for xml path('RecordSet'), TYPE)
			--, (SELECT * FROM inserted as Record for xml auto, elements , root('RecordSet'), type)

	END


	-- Detect deletes
	IF EXISTS (select * from deleted) AND NOT EXISTS (select * from inserted)
	BEGIN

		insert into dbo.AuditJobChanges (AuditChangeDate
										, AuditChangeType
										, AuditChangePerformedBy
										, ApplicationName
										, HostName
										, SchemaName
										, ObjectName
										, XML_RECSET
										)
			select getdate()
			, 'DELETE'
			, @lv_sUserSName
			, @lv_applicationName
			, @lv_hostName
			, @lv_schemaName
			, @lv_tablename
			, XML_RECSET = (select * from deleted for xml path('RecordSet'), TYPE)
			--, (SELECT * FROM deleted as Record for xml auto, elements , root('RecordSet'), type)

	END


	-- Detect updates
	IF EXISTS (select * from inserted) AND EXISTS (select * from deleted)
	BEGIN

		insert into dbo.AuditJobChanges (AuditChangeDate
										, AuditChangeType
										, AuditChangePerformedBy
										, ApplicationName
										, HostName
										, SchemaName
										, ObjectName
										, XML_RECSET
										)
			select getdate()
			, 'UPDATE'
			, @lv_sUserSName
			, @lv_applicationName
			, @lv_hostName
			, @lv_schemaName
			, @lv_tablename
			, XML_RECSET = (select * from inserted for xml path('RecordSet'), TYPE)
			--, (SELECT * FROM deleted as Record for xml auto, elements , root('RecordSet'), type)

	END

END






END
GO




if object_id('[dbo].[trig_sysjobsteps_insert_update_delete]') is not null
	drop trigger [dbo].[trig_sysjobsteps_insert_update_delete]
go

CREATE TRIGGER [dbo].[trig_sysjobsteps_insert_update_delete]
ON [dbo].[sysjobsteps]
FOR INSERT, UPDATE, DELETE
AS

--************************************************************************
--
-- Vers Who       Date       What
-- ---- --------- ---------- -------------------------
-- 1.0  ggable    09/21/2017 Initial Release
-- 1.1  ggable    09/28/2017 added condition not to insert when change comes from system automated update ('SQLAgent - Job Manager')
--************************************************************************

BEGIN

declare @lv_schemaName varchar(25) = 'dbo'
, @lv_tablename varchar(50) = 'sysjobsteps'



declare @lv_userName nvarchar(50) = user_name()
, @lv_sUserSName nvarchar(50) = suser_sname()
, @lv_originalLogin nvarchar(50) = original_login()
, @lv_applicationName nvarchar(50) = app_name()
, @lv_hostName nvarchar(50) = host_name()
, @lb_IncludeSystemAutomedUpdates bit = 0

select @lv_userName = user_name()
select @lv_sUserSName = suser_sname()
select @lv_originalLogin = original_login()
select @lv_applicationName = app_name()
select @lv_hostName = host_name()


IF @lv_applicationName <> 'SQLAgent - Job Manager' OR @lb_IncludeSystemAutomedUpdates = 1 BEGIN

	-- Detect inserts
	IF EXISTS (select * from inserted) AND NOT EXISTS (select * from deleted)
	BEGIN

		insert into dbo.AuditJobChanges (AuditChangeDate
										, AuditChangeType
										, AuditChangePerformedBy
										, ApplicationName
										, HostName
										, SchemaName
										, ObjectName
										, XML_RECSET
										)
			select getdate()
			, 'INSERT'
			, @lv_sUserSName
			, @lv_applicationName
			, @lv_hostName
			, @lv_schemaName
			, @lv_tablename
			, XML_RECSET = (select * from inserted for xml path('RecordSet'), TYPE)
			--, (SELECT * FROM inserted as Record for xml auto, elements , root('RecordSet'), type)

	END


	-- Detect deletes
	IF EXISTS (select * from deleted) AND NOT EXISTS (select * from inserted)
	BEGIN

		insert into dbo.AuditJobChanges (AuditChangeDate
										, AuditChangeType
										, AuditChangePerformedBy
										, ApplicationName
										, HostName
										, SchemaName
										, ObjectName
										, XML_RECSET
										)
			select getdate()
			, 'DELETE'
			, @lv_sUserSName
			, @lv_applicationName
			, @lv_hostName
			, @lv_schemaName
			, @lv_tablename
			, XML_RECSET = (select * from deleted for xml path('RecordSet'), TYPE)
			--, (SELECT * FROM deleted as Record for xml auto, elements , root('RecordSet'), type)

	END


	-- Detect updates
	IF EXISTS (select * from inserted) AND EXISTS (select * from deleted)
	BEGIN

		insert into dbo.AuditJobChanges (AuditChangeDate
										, AuditChangeType
										, AuditChangePerformedBy
										, ApplicationName
										, HostName
										, SchemaName
										, ObjectName
										, XML_RECSET
										)
			select getdate()
			, 'UPDATE'
			, @lv_sUserSName
			, @lv_applicationName
			, @lv_hostName
			, @lv_schemaName
			, @lv_tablename
			, XML_RECSET = (select * from inserted for xml path('RecordSet'), TYPE)
			--, (SELECT * FROM deleted as Record for xml auto, elements , root('RecordSet'), type)

	END


END




END
GO





if object_id('[dbo].[trig_sysjobschedules_insert_update_delete]') is not null
	drop trigger [dbo].[trig_sysjobschedules_insert_update_delete]
go

CREATE TRIGGER [dbo].[trig_sysjobschedules_insert_update_delete]
ON [dbo].[sysjobschedules]
FOR INSERT, UPDATE, DELETE
AS

--************************************************************************
--
-- Vers Who       Date       What
-- ---- --------- ---------- -------------------------
-- 1.0  ggable    09/21/2017 Initial Release
-- 1.1  ggable    09/28/2017 added condition not to insert when change comes from system automated update ('SQLAgent - Job Manager')
--************************************************************************

BEGIN

declare @lv_schemaName varchar(25) = 'dbo'
, @lv_tablename varchar(50) = 'sysjobschedules'



declare @lv_userName nvarchar(50) = user_name()
, @lv_sUserSName nvarchar(50) = suser_sname()
, @lv_originalLogin nvarchar(50) = original_login()
, @lv_applicationName nvarchar(50) = app_name()
, @lv_hostName nvarchar(50) = host_name()
, @lb_IncludeSystemAutomedUpdates bit = 0

select @lv_userName = user_name()
select @lv_sUserSName = suser_sname()
select @lv_originalLogin = original_login()
select @lv_applicationName = app_name()
select @lv_hostName = host_name()



IF @lv_applicationName <> 'SQLAgent - Job Manager' OR @lb_IncludeSystemAutomedUpdates = 1 BEGIN

	-- Detect inserts
	IF EXISTS (select * from inserted) AND NOT EXISTS (select * from deleted)
	BEGIN

		insert into dbo.AuditJobChanges (AuditChangeDate
										, AuditChangeType
										, AuditChangePerformedBy
										, ApplicationName
										, HostName
										, SchemaName
										, ObjectName
										, XML_RECSET
										)
			select getdate()
			, 'INSERT'
			, @lv_sUserSName
			, @lv_applicationName
			, @lv_hostName
			, @lv_schemaName
			, @lv_tablename
			, XML_RECSET = (select * from inserted for xml path('RecordSet'), TYPE)
			--, (SELECT * FROM inserted as Record for xml auto, elements , root('RecordSet'), type)

	END


	-- Detect deletes
	IF EXISTS (select * from deleted) AND NOT EXISTS (select * from inserted)
	BEGIN

		insert into dbo.AuditJobChanges (AuditChangeDate
										, AuditChangeType
										, AuditChangePerformedBy
										, ApplicationName
										, HostName
										, SchemaName
										, ObjectName
										, XML_RECSET
										)
			select getdate()
			, 'DELETE'
			, @lv_sUserSName
			, @lv_applicationName
			, @lv_hostName
			, @lv_schemaName
			, @lv_tablename
			, XML_RECSET = (select * from deleted for xml path('RecordSet'), TYPE)
			--, (SELECT * FROM deleted as Record for xml auto, elements , root('RecordSet'), type)

	END


	-- Detect updates
	IF EXISTS (select * from inserted) AND EXISTS (select * from deleted)
	BEGIN

		insert into dbo.AuditJobChanges (AuditChangeDate
										, AuditChangeType
										, AuditChangePerformedBy
										, ApplicationName
										, HostName
										, SchemaName
										, ObjectName
										, XML_RECSET
										)
			select getdate()
			, 'UPDATE'
			, @lv_sUserSName
			, @lv_applicationName
			, @lv_hostName
			, @lv_schemaName
			, @lv_tablename
			, XML_RECSET = (select * from inserted for xml path('RecordSet'), TYPE)
			--, (SELECT * FROM deleted as Record for xml auto, elements , root('RecordSet'), type)

	END


END






END
GO

 

Step 4

Now that the triggers are in place, any changes will be logged into the audit table we created. You will see that the table includes XML formatted values of the change.  Therefore, the last step here is querying the data.  The following 2 queries are for viewing the changes to the sysjobs table and sysjobsteps schedule.  It pulls the data out of the xml file for easy reference.

if object_id('tempdb..#jobTracking') is not null
	drop table #jobTracking
go

-- Job level changes tracking
select x.AuditChangeID
, x.AuditChangeDate
, x.AuditChangeType
, x.AuditChangePerformedBy
, x.ApplicationName
, x.HostName
, x.job_id
, coalesce(j.name, x.name) as name
, case x.[enabled] when 1 then 'Y' when 0 then 'N' else null end as [Enabled]
, x.start_step_id
--, x.notify_email_operator_id
, o.name as EmailOperator
, case x.notify_level_email when 1 then 'When the job succeeds' when 2 then 'When the job fails' when 3 then 'When the job completes' else null end as EmailOperatorWhen
, x.xml_recset
into #jobTracking
from (	
		SELECT ajc.AuditChangeID
		, ajc.AuditChangeDate
		, ajc.AuditChangeType
		, ajc.AuditChangePerformedBy
		, ajc.ApplicationName
		, ajc.HostName
		, r.c.value('(job_id/text())[1]', 'UNIQUEIDENTIFIER' ) AS job_id
		, r.c.value('(name/text())[1]', 'SYSNAME') as name
		, r.c.value('(enabled/text())[1]', 'BIT') as [enabled]
		, r.c.value('(start_step_id/text())[1]', 'SMALLINT') as start_step_id
		, r.c.value('(notify_email_operator_id/text())[1]', 'SMALLINT') as notify_email_operator_id
		, r.c.value('(notify_level_email/text())[1]', 'SMALLINT') as notify_level_email
		, ajc.xml_recset
		FROM dbo.AuditJobChanges as ajc
		CROSS APPLY ajc.XML_RECSET.nodes('RecordSet') r(c)
		WHERE ajc.schemaname = 'dbo' and ajc.objectname = 'sysjobs'
	) as x
left join dbo.sysjobs as j on x.job_id = j.job_id
left join dbo.sysoperators as o on j.notify_email_operator_id = o.id
ORDER BY x.AuditChangeID

select * from #jobTracking order by AuditChangeID


-- Job steps level changes tracking
select x.AuditChangeID
, x.AuditChangeDate
, x.AuditChangeType
, x.AuditChangePerformedBy
, x.ApplicationName
, x.HostName
, x.job_id
, coalesce(j.name, j2.name) as name
, x.step_id
, x.step_name
, x.subsystem
, x.command
, x.database_name
--, x.on_success_action
--, x.on_success_step_id
, case x.on_success_action
		when 3 then 'Go to the next step' 
		when 2 then 'Quit the job reporting failure'
		when 1 then 'Quit the job reporting success'
		when 4 then 'Go to step ' + cast(x.on_success_step_id as varchar)
		else null
  end as OnSuccessAction
--, x.on_fail_action
--, x.on_fail_step_id
, case x.on_fail_action
		when 3 then 'Go to the next step' 
		when 2 then 'Quit the job reporting failure'
		when 1 then 'Quit the job reporting success'
		when 4 then 'Go to step ' + cast(x.on_fail_step_id as varchar)
		else null
  end as OnFailureAction
, x.xml_recset
from (	
		SELECT ajc.AuditChangeID
		, ajc.AuditChangeDate
		, ajc.AuditChangeType
		, ajc.AuditChangePerformedBy
		, ajc.ApplicationName
		, ajc.HostName
		, r.c.value('(job_id/text())[1]', 'UNIQUEIDENTIFIER' ) AS job_id
		, r.c.value('(step_id/text())[1]', 'SMALLINT') as step_id
		, r.c.value('(step_name/text())[1]', 'VARCHAR(150)') as step_name
		, r.c.value('(subsystem/text())[1]', 'VARCHAR(50)') as subsystem
		, r.c.value('(command/text())[1]', 'VARCHAR(MAX)') as command
		, r.c.value('(database_name/text())[1]', 'VARCHAR(50)') as database_name
		, r.c.value('(on_success_action/text())[1]', 'SMALLINT') as on_success_action
		, r.c.value('(on_success_step_id/text())[1]', 'SMALLINT') as on_success_step_id
		, r.c.value('(on_fail_action/text())[1]', 'SMALLINT') as on_fail_action
		, r.c.value('(on_fail_step_id/text())[1]', 'SMALLINT') as on_fail_step_id
		, ajc.xml_recset
		FROM dbo.AuditJobChanges as ajc
		CROSS APPLY ajc.XML_RECSET.nodes('RecordSet') r(c)
		WHERE ajc.schemaname = 'dbo' and ajc.objectname = 'sysjobsteps'
	) as x
left join dbo.sysjobs as j on x.job_id = j.job_id
--join dbo.sysjobsteps as js on x.job_id = js.job_id and x.step_id = js.step_id
left join (select distinct job_id, name from #jobTracking) as j2 on x.job_id = j2.job_id

I hope this helps someone who is looking for a quick solution, while researching and taking their time with another solution.  Let me know if you have any questions!