Simple Audit of SQL Server Agent: @SpiceWorks

Who Changed My SQL Server Agent Job?

Created process and ssrs report to audit changes to SQL Server Agent Jobs on SQL Server 2008 R2. Created process using SSRS, SSMS, T-SQL, SQL Triggers.

Created and implemented process to audit changes to SQL Server Agent Jobs on SQL Server 2008 R2. Created a new schema and table structure to capture audit data for agent jobs. Created triggers on the sysjobs, sysjobsteps, sysjobschedule tables that captured imports, updates and deletes made to sql server agent jobs and then loaded the data, as well as the user realted to the activity, to the newly created audit table. I also created a SSRS report that allowed the Information Services team to view the audit data.

1 Week Duration
1-49 Users
1-49 Devices

/** Description : Create SQL Angent Jobs Audit Process without SQL Servers “Audit” feature
Author : Wylie Blanchard
Date : 2016/02/12 Updated : 2016/03/18 **/
/** Create Schema [audit]**/
USE [AuditDB]
GO
CREATE SCHEMA [audit] AUTHORIZATION [dbo]
GO
/** Create table [audit].[SQLJobModificationHistory]**/
USE [AuditDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [audit].[SQLJobModificationHistory](
[EventRowID] [bigint] IDENTITY(1,1) NOT NULL,
[EventTime] [datetime] NULL,
[EventType] [varchar](128) NULL,
[Message] [varchar](max) NULL,
[HostName] [varchar](128) NULL,
[Instance] [varchar](128) NULL,
[JobID] [varchar](256) NULL,
[JobName] [varchar](256) NULL,
[OldJobName] [varchar](256) NULL,
[JobCreationDate] [datetime] NULL,
[JobModificationDate] [datetime] NULL,
[AuditUser] [varchar](128) NULL,
[SessionLoginName] [varchar](256) NULL,
[AuditDate] [smalldatetime] NOT NULL,
CONSTRAINT [PK_SQLJobModificationHistory_EventRowID] PRIMARY KEY NONCLUSTERED
(
[EventRowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

SET ANSI_PADDING OFF
GO

ALTER TABLE [audit].[SQLJobModificationHistory] ADD CONSTRAINT [DF_SQLJobModificationHistory_SessionLoginName] DEFAULT (original_login()) FOR [SessionLoginName]
GO

ALTER TABLE [audit].[SQLJobModificationHistory] ADD CONSTRAINT [DF_SQLJobModificationHistory_AuditDate] DEFAULT (getdate()) FOR [AuditDate]
GO

/** Create Audit Triggers on SysJobs**/

— =============================================
— Author: <Wylie Blanchard>
— Create date: <2016-02-15>
— Description: –Create Trigger AuditNewSQLAgentJobTrigger
— =============================================
USE [msdb]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[AuditNewSQLAgentJobTrigger]
ON [msdb].[dbo].[sysjobs]
FOR INSERT

AS BEGIN

BEGIN TRY

SELECT @UserName = SYSTEM_USER
SELECT @SessionLogin = ORIGINAL_LOGIN()
SELECT @HostName = HOST_NAME()
SELECT @NewJobID = [job_id] FROM Inserted
SELECT @NewJobName = [name] FROM Inserted
SELECT @SQLInstance = CONVERT([varchar](128), SERVERPROPERTY(‘ServerName’))
SELECT @DateJobCreated = [date_created] FROM Inserted
SELECT @DateJobModified = [date_modified] FROM Inserted
IF(SELECT COUNT([name]) FROM [master].[dbo].[sysdatabases]
WHERE [name] IN (‘AuditDB’)
AND [status]&32 <> 32 AND [status]&256 <> 256
AND [status]&32768 <> 32768
AND DATABASEPROPERTYEX([name],’Status’) NOT IN ( ‘OFFLINE’
,’RESTORING’
,’RECOVERING’
,’SUSPECT’) ) = 1
BEGIN
INSERT INTO [AuditDB].[audit].[SQLJobModificationHistory]
([EventTime]
,[EventType]
,[Message]
,[HostName]
,[Instance]
,[JobID]
,[JobName]
,[OldJobName]
,[JobCreationDate]
,[JobModificationDate]
,[AuditUser]
,[SessionLoginName])
VALUES
(GETDATE()
,’JOB_CREATED’
,CAST ( ‘User “‘+@UserName+’” has created SQL job “‘+@NewJobName+’” from host “‘+@HostName+’” at ‘
+CONVERT(VARCHAR(20),GETDATE(),100) AS varchar(max))
,@HostName
,@SQLInstance
,@NewJobID
,@NewJobName
,’– Not Applicable –’
,@DateJobCreated
,@DateJobModified
,@UserName
,@SessionLogin)
END
COMMIT TRANSACTION;
END TRY

BEGIN CATCH
— Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
PRINT
N’The transaction is in an uncommittable state. ‘ +
‘Rolling back transaction.’
ROLLBACK TRANSACTION;
END;
— Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT
N’The transaction is committable. ‘ +
‘Committing transaction.’
COMMIT TRANSACTION;
END;

DECLARE @ErrorMessage [nvarchar](4000);
DECLARE @ErrorSeverity [int];
DECLARE @ErrorState [int];

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

— RAISERROR inside the CATCH block to return error
— information about the original error that caused
— execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, — Message text.
@ErrorSeverity, — Severity.
@ErrorState ); — State.
END CATCH
END
GO
— =============================================
— Author: <Wylie Blanchard>
— Create date: <2016-02-15>
— Description: –Create Trigger AuditModifiedSQLAgentJobTrigger
— =============================================
USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[AuditModifiedSQLAgentJobTrigger]
ON [msdb].[dbo].[sysjobs]
FOR UPDATE

AS BEGIN

BEGIN TRY

SELECT @UserName = SYSTEM_USER
SELECT @SessionLogin = ORIGINAL_LOGIN()
SELECT @HostName = HOST_NAME()
SELECT @NewJobID = [job_id] FROM Inserted
SELECT @NewJobName = [name] FROM Inserted
SELECT @OldJobName = [name] FROM Deleted
SELECT @SQLInstance = CONVERT([varchar](128), SERVERPROPERTY(‘ServerName’))
SELECT @DateJobCreated = [date_created] FROM Inserted
SELECT @DateJobModified = [date_modified] FROM Inserted
IF(SELECT COUNT([name]) FROM [master].[dbo].[sysdatabases]
WHERE [name] IN (‘AuditDB’)
AND [status]&32 <> 32 AND [status]&256 <> 256
AND [status]&32768 <> 32768
AND DATABASEPROPERTYEX([name],’Status’) NOT IN ( ‘OFFLINE’
,’RESTORING’
,’RECOVERING’
,’SUSPECT’) ) = 1
BEGIN
IF (@NewJobName is not null and @OldJobName is not null) and (@NewJobName <> @OldJobName)
INSERT INTO [AuditDB].[audit].[SQLJobModificationHistory]
([EventTime]
,[EventType]
,[Message]
,[HostName]
,[Instance]
,[JobID]
,[JobName]
,[OldJobName]
,[JobCreationDate]
,[JobModificationDate]
,[AuditUser]
,[SessionLoginName])
VALUES
(GETDATE()
,’JOB_MODIFIED’
, CAST ( ‘User “‘+@UserName+’” has modified job “‘+@OldJobName+’” with “‘
+@NewJobName+’” from host “‘+@HostName+’” at ‘
+CONVERT(VARCHAR(20),GETDATE(),100) AS varchar(max))
,@HostName
,@SQLInstance
,@NewJobID
,@NewJobName
,@OldJobName
,@DateJobCreated
,@DateJobModified
,@UserName
,@SessionLogin)
END
COMMIT TRANSACTION;
END TRY

DECLARE @ErrorMessage [nvarchar](4000);
DECLARE @ErrorSeverity [int];
DECLARE @ErrorState [int];

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

— RAISERROR inside the CATCH block to return error
— information about the original error that caused
— execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, — Message text.
@ErrorSeverity, — Severity.
@ErrorState ); — State.
END CATCH
END
GO
— =============================================
— Author: <Wylie Blanchard>
— Create date: <2016-02-15>
— Description: –Create Trigger AuditDeletedSQLAgentJobTrigger
— =============================================
USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[AuditDeletedSQLAgentJobTrigger]
ON [msdb].[dbo].[sysjobs]
FOR DELETE
AS BEGIN

BEGIN TRY

BEGIN TRANSACTION
SET NOCOUNT ON

SELECT @UserName = SYSTEM_USER
SELECT @SessionLogin = ORIGINAL_LOGIN()
SELECT @HostName = HOST_NAME()
SELECT @OldJobID = [job_id] FROM Deleted
SELECT @OldJobName = [name] FROM Deleted
SELECT @SQLInstance = CONVERT([varchar](128), SERVERPROPERTY(‘ServerName’))
SELECT @DateJobCreated = [date_created] FROM Deleted
SELECT @DateJobModified = [date_modified] FROM Deleted

IF(SELECT COUNT([name]) FROM [master].[dbo].[sysdatabases]
WHERE [name] IN (‘AuditDB’)
AND [status]&32 <> 32 AND [status]&256 <> 256
AND [status]&32768 <> 32768
AND DATABASEPROPERTYEX([name],’Status’) NOT IN ( ‘OFFLINE’
,’RESTORING’
,’RECOVERING’
,’SUSPECT’) ) = 1
BEGIN
INSERT INTO [AuditDB].[audit].[SQLJobModificationHistory]
([EventTime]
,[EventType]
,[Message]
,[HostName]
,[Instance]
,[JobID]
,[JobName]
,[OldJobName]
,[JobCreationDate]
,[JobModificationDate]
,[AuditUser]
,[SessionLoginName])
VALUES
(GETDATE()
,’JOB_DELETED’
,CAST ( ‘User “‘+@UserName+’” has deleted SQL job “‘+@OldJobName+’” from “‘+@HostName+’” at ‘
+CONVERT(VARCHAR(20),GETDATE(),100) AS varchar(max))
,@HostName
,@SQLInstance
,@OldJobID
,@OldJobName
,’– Not Applicable –’
,@DateJobCreated
,@DateJobModified
,@UserName
,@SessionLogin)
END
COMMIT TRANSACTION;
END TRY

DECLARE @ErrorMessage [nvarchar](4000);
DECLARE @ErrorSeverity [int];
DECLARE @ErrorState [int];

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

— RAISERROR inside the CATCH block to return error
— information about the original error that caused
— execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, — Message text.
@ErrorSeverity, — Severity.
@ErrorState ); — State.
END CATCH
END
GO
— =============================================
— Author: <Wylie Blanchard>
— Create date: <2016-02-15>
— Description: –Create Trigger AuditSQLAgentJobActivationTrigger
— =============================================
USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[AuditSQLAgentJobActivationTrigger]
ON [msdb].[dbo].[sysjobs]
FOR UPDATE

AS BEGIN

BEGIN TRY

SELECT @UserName = SYSTEM_USER
SELECT @SessionLogin = ORIGINAL_LOGIN()
SELECT @HostName = HOST_NAME()
SELECT @NewJobID = [job_id] FROM Inserted
SELECT @NewJobName = [name] FROM Inserted
SELECT @OldJobName = [name] FROM Deleted
SELECT @NewEnabled = [enabled] FROM Inserted
SELECT @OldEnabled = [enabled] FROM Deleted
SELECT @SQLInstance = CONVERT([varchar](128), SERVERPROPERTY(‘ServerName’))
SELECT @DateJobCreated = [date_created] FROM Inserted
SELECT @DateJobModified = [date_modified] FROM Inserted

— check if the enabled flag has been updated.
IF @NewEnabled <> @OldEnabled
BEGIN
IF(SELECT COUNT([name]) FROM [master].[dbo].[sysdatabases]
WHERE [name] IN (‘AuditDB’)
AND [status]&32 <> 32 AND [status]&256 <> 256
AND [status]&32768 <> 32768
AND DATABASEPROPERTYEX([name],’Status’) NOT IN ( ‘OFFLINE’
,’RESTORING’
,’RECOVERING’
,’SUSPECT’) ) = 1
BEGIN
IF @NewEnabled = 1
BEGIN
INSERT INTO [AuditDB].[audit].[SQLJobModificationHistory]
([EventTime]
,[EventType]
,[Message]
,[HostName]
,[Instance]
,[JobID]
,[JobName]
,[OldJobName]
,[JobCreationDate]
,[JobModificationDate]
,[AuditUser]
,[SessionLoginName])
VALUES
(GETDATE()
,’JOB_ENABLED’
,CAST ( ‘User “‘+@UserName+’” has enabled SQL job “‘+@NewJobName+’” from host “‘+@HostName+’” at ‘
+CONVERT(VARCHAR(20),GETDATE(),100) AS varchar(max))
,@HostName
,@SQLInstance
,@NewJobID
,@NewJobName
,@OldJobName
,@DateJobCreated
,@DateJobModified
,@UserName
,@SessionLogin)
END — End of inner-IF block…
IF @NewEnabled = 0
BEGIN
INSERT INTO [AuditDB].[audit].[SQLJobModificationHistory]
([EventTime]
,[EventType]
,[Message]
,[HostName]
,[Instance]
,[JobID]
,[JobName]
,[OldJobName]
,[JobCreationDate]
,[JobModificationDate]
,[AuditUser]
,[SessionLoginName])
VALUES
(GETDATE()
,’JOB_DISABLED’
,CAST ( ‘User “‘+@UserName+’” has disabled SQL job “‘+@NewJobName+’” from host “‘+@HostName+’” at ‘
+CONVERT(VARCHAR(20),GETDATE(),100) AS varchar(max))
,@HostName
,@SQLInstance
,@NewJobID
,@NewJobName
,@OldJobName
,@DateJobCreated
,@DateJobModified
,@UserName
,@SessionLogin)
END — End of inner-IF block…
END — End of outer-IF block…
END — End of outer-IF block…
COMMIT TRANSACTION;
END TRY

DECLARE @ErrorMessage [nvarchar](4000);
DECLARE @ErrorSeverity [int];
DECLARE @ErrorState [int];

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

GO
/** Create Audit Triggers on SysJobSteps**/

— =============================================
— Author: <Wylie Blanchard>
— Create date: <2016-02-15>
— Description: –Create Trigger AuditNewSQLAgentJobStepTrigger
— =============================================
USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[AuditNewSQLAgentJobStepTrigger]
ON [msdb].[dbo].[sysjobsteps]
FOR INSERT

AS BEGIN

BEGIN TRY

BEGIN
INSERT INTO [AuditDB].[audit].[SQLJobModificationHistory]
([EventTime]
,[EventType]
,[Message]
,[HostName]
,[Instance]
,[JobID]
,[JobName]
,[OldJobName]
,[JobCreationDate]
,[JobModificationDate]
,[AuditUser]
,[SessionLoginName])
VALUES
(GETDATE()
,’STEP_CREATED’
, CAST ( ‘User “‘+@UserName+’” has created job step “‘+@NewStepName+’” of SQL Job “‘
+@NewJobName+’” from host “‘+@HostName+’” at ‘+CONVERT(VARCHAR(20),GETDATE(),100)
+’ New command is “‘+@New_cmd+’”‘ AS varchar(max))
,@HostName
,@SQLInstance
,@NewJobID
,@NewJobName
,’– Not Applicable –’
,@DateJobCreated
,@DateJobModified
,@UserName
,@SessionLogin)

END
COMMIT TRANSACTION;
END TRY

DECLARE @ErrorMessage [nvarchar](4000);
DECLARE @ErrorSeverity [int];
DECLARE @ErrorState [int];

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

— RAISERROR inside the CATCH block to return error
— information about the original error that caused
— execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, — Message text.
@ErrorSeverity, — Severity.
@ErrorState ); — State.
END CATCH
END
GO
— =============================================
— Author: <Wylie Blanchard>
— Create date: <2016-02-15>
— Description: –Create Trigger AuditModifiedSQLAgentJobStepTrigger
— =============================================
USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[AuditModifiedSQLAgentJobStepTrigger]
ON [msdb].[dbo].[sysjobsteps]
FOR UPDATE

AS BEGIN

BEGIN TRY

IF @NewStepName <> @OldStepName
BEGIN
INSERT INTO [AuditDB].[audit].[SQLJobModificationHistory]
([EventTime]
,[EventType]
,[Message]
,[HostName]
,[Instance]
,[JobID]
,[JobName]
,[OldJobName]
,[JobCreationDate]
,[JobModificationDate]
,[AuditUser]
,[SessionLoginName])
VALUES
(GETDATE()
,’STEP_MODIFIED’
,–CAST ( ‘StepID=”‘ + @StepId + ‘”‘ +
–‘, StepName=”‘ + @StepName + ‘”‘ +
–‘, SubSystem=”‘ + @SubSystem + ‘”‘ +
— ‘, Command=”‘ + @Command + ‘”‘ AS varchar(max))

CAST ( ‘User “‘+@UserName+’” has modified job step “‘+@OldStepName+’” with “‘
+@NewStepName+’” of SQL Job “‘+@NewJobName+’” from host “‘+@HostName+’” at ‘
+CONVERT(VARCHAR(20),GETDATE(),100) +’. Old command is “‘+@Old_cmd+’”‘ AS varchar(max))
,@HostName
,@SQLInstance
,@OldJobID
,@NewJobName
,@OldJobName
,@DateJobCreated
,@DateJobModified
,@UserName
,@SessionLogin)
END

COMMIT TRANSACTION;
END TRY

DECLARE @ErrorMessage [nvarchar](4000);
DECLARE @ErrorSeverity [int];
DECLARE @ErrorState [int];

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

GO
— =============================================
— Author: <Wylie Blanchard>
— Create date: <2016-02-15>
— Description: –Create Trigger AuditDeletedSQLAgentJobStepTrigger
— =============================================
USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[AuditDeletedSQLAgentJobStepTrigger]
ON [msdb].[dbo].[sysjobsteps]
FOR DELETE
AS BEGIN

BEGIN TRY

BEGIN TRANSACTION
SET NOCOUNT ON

DECLARE
@UserName [varchar](256)
,@SessionLogin [varchar](128)
,@HostName [varchar](128)
,@OldJobID [varchar](256)
,@OldJobName [varchar](256)
,@SQLInstance [varchar](128)
,@DateJobCreated [datetime]
,@DateJobModified [datetime]
,@OldStepId [varchar] (40)
,@OldStepName [varchar] (128)
,@SubSystem [varchar] (40)
,@Old_cmd [varchar](MAX)

SELECT @UserName = SYSTEM_USER
SELECT @SessionLogin = ORIGINAL_LOGIN()
SELECT @HostName = HOST_NAME()
SELECT @OldJobID = [job_id] FROM Deleted
SELECT @OldJobName = [name] FROM Deleted LEFT JOIN [sysjobs] ON Deleted.[job_id] = [sysjobs].[job_id]
SELECT @SQLInstance = CONVERT([varchar](128), SERVERPROPERTY(‘ServerName’))
SELECT @DateJobCreated = [date_created] FROM Deleted LEFT JOIN [sysjobs] ON Deleted.[job_id] = [sysjobs].[job_id]
SELECT @DateJobModified = [date_modified] FROM Deleted LEFT JOIN [sysjobs] ON Deleted.[job_id] = [sysjobs].[job_id]
SELECT @OldStepId = [step_id] FROM Deleted
SELECT @OldStepName = [step_name] FROM Deleted
SELECT @SubSystem = [subsystem] FROM Deleted
SELECT @Old_cmd = ISNULL(command,’– Not Applicable –’) FROM Deleted

IF(SELECT COUNT([name]) FROM [master].[dbo].[sysdatabases]
WHERE [name] IN (‘AuditDB’)
AND [status]&32 <> 32 AND [status]&256 <> 256
AND [status]&32768 <> 32768
AND DATABASEPROPERTYEX([name],’Status’) NOT IN ( ‘OFFLINE’
,’RESTORING’
,’RECOVERING’
,’SUSPECT’) ) = 1
BEGIN
INSERT INTO [AuditDB].[audit].[SQLJobModificationHistory]
([EventTime]
,[EventType]
,[Message]
,[HostName]
,[Instance]
,[JobID]
,[JobName]
,[OldJobName]
,[JobCreationDate]
,[JobModificationDate]
,[AuditUser]
,[SessionLoginName])
VALUES
(GETDATE()
,’STEP_DELETED’
, CAST ( ‘User “‘+@UserName+’” has deleted job step “‘+@OldStepName+’” of SQL Job “‘+@OldJobName+’” from host “‘
+@HostName+’” at ‘+CONVERT(VARCHAR(20),GETDATE(),100) +’ with command “‘+@Old_cmd+’”‘ AS varchar(max))
,@HostName
,@SQLInstance
,@OldJobID
,@OldJobName
,’– Not Applicable –’
,@DateJobCreated
,@DateJobModified
,@UserName
,@SessionLogin)
END
COMMIT TRANSACTION;
END TRY

DECLARE @ErrorMessage [nvarchar](4000);
DECLARE @ErrorSeverity [int];
DECLARE @ErrorState [int];

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

— RAISERROR inside the CATCH block to return error
— information about the original error that caused
— execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, — Message text.
@ErrorSeverity, — Severity.
@ErrorState ); — State.
END CATCH
END
GO
— =============================================
— Author: <Wylie Blanchard>
— Create date: <2016-02-15>
— Description: –Create Trigger AuditModifiedSQLAgentJobStepCmdTrigger
— =============================================
USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[AuditModifiedSQLAgentJobStepCmdTrigger]
ON [msdb].[dbo].[sysjobsteps]
FOR UPDATE

AS BEGIN

BEGIN TRY

IF @New_cmd <> @old_cmd –Change to Command
BEGIN
INSERT INTO [AuditDB].[audit].[SQLJobModificationHistory]
([EventTime]
,[EventType]
,[Message]
,[HostName]
,[Instance]
,[JobID]
,[JobName]
,[OldJobName]
,[JobCreationDate]
,[JobModificationDate]
,[AuditUser]
,[SessionLoginName])
VALUES
(GETDATE()
,’STEP_CMD_MODIFIED’
,CAST ( ‘User “‘+@UserName+’” has modified job step “‘+@OldStepName
+’” of SQL Job “‘+@OldJobName+’” from host “‘+@HostName+’” at ‘
+CONVERT(VARCHAR(20),GETDATE(),100) +’. Old command is “‘+@Old_cmd+’”‘ AS varchar(max))
,@HostName
,@SQLInstance
,@OldJobID
,@NewJobName
,@OldJobName
,@DateJobCreated
,@DateJobModified
,@UserName
,@SessionLogin)
END

COMMIT TRANSACTION;
END TRY

DECLARE @ErrorMessage [nvarchar](4000);
DECLARE @ErrorSeverity [int];
DECLARE @ErrorState [int];

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

GO
/** Create Audit Triggers on SysJobSchedules**/

— =============================================
— Author: <Wylie Blanchard>
— Create date: <2016-02-15>
— Description: –Create Trigger AuditNewSQLAgentJobScheduleTrigger
— =============================================
USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[AuditNewSQLAgentJobScheduleTrigger]
ON [msdb].[dbo].[sysjobschedules]
FOR INSERT

AS BEGIN

BEGIN TRY

BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE
@UserName [varchar](256)
,@SessionLogin [varchar](128)
,@HostName [varchar](128)
,@NewJobID [varchar](256)
,@NewJobName [varchar](256)
,@SQLInstance [varchar](128)
,@DateJobCreated [datetime]
,@DateJobModified [datetime]
,@NewScheduleName [varchar] (128)
,@NewFrequency [varchar](MAX)
,@NewScheduleID [int]
SELECT @UserName = SYSTEM_USER
SELECT @SessionLogin = ORIGINAL_LOGIN()
SELECT @HostName = HOST_NAME()
SELECT @NewJobID = [job_id] FROM INSERTED
—–
SELECT @SQLInstance = CONVERT([varchar](128), SERVERPROPERTY(‘ServerName’))
SELECT @DateJobCreated = [sysjobs].[date_created] FROM INSERTED LEFT JOIN [sysjobs] ON INSERTED.[job_id] = [sysjobs].[job_id]
SELECT @DateJobModified = [sysjobs].[date_modified] FROM INSERTED LEFT JOIN [sysjobs] ON INSERTED.[job_id] = [sysjobs].[job_id]
SELECT @NewScheduleName = [name] FROM INSERTED LEFT JOIN [sysschedules] on INSERTED.[schedule_id] = [sysschedules].[schedule_id]
SELECT @NewFrequency = RTRIM(CAST ( CASE [freq_type] WHEN 1 THEN ‘Occurs Only Once’ WHEN 4 THEN ‘Occurs Every ‘ + CONVERT(VARCHAR, [freq_interval]) + ‘ day(s)’ WHEN 8 THEN ‘Occurs Every ‘ + CONVERT(VARCHAR, [freq_recurrence_factor]) + ‘ weeks(s) on ‘ + LEFT( CASE WHEN [freq_interval] & 1 = 1 THEN ‘Sunday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 2 = 2 THEN ‘Monday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 4 = 4 THEN ‘Tuesday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 8 = 8 THEN ‘Wednesday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 16 = 16 THEN ‘Thursday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 32 = 32 THEN ‘Friday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 64 = 64 THEN ‘Saturday, ‘ ELSE ” END , LEN( CASE WHEN [freq_interval] & 1 = 1 THEN ‘Sunday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 2 = 2 THEN ‘Monday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 4 = 4 THEN ‘Tuesday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 8 = 8 THEN ‘Wednesday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 16 = 16 THEN ‘Thursday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 32 = 32 THEN ‘Friday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 64 = 64 THEN ‘Saturday, ‘ ELSE ” END ) – 1 ) WHEN 16 THEN ‘Occurs Day ‘ + CONVERT(VARCHAR, [freq_interval]) + ‘ of every ‘ + CONVERT(VARCHAR, [freq_recurrence_factor]) + ‘ month(s)’ WHEN 32 THEN ‘Occurs on the ‘ + CASE [freq_relative_interval] WHEN 1 THEN ‘First’ WHEN 2 THEN ‘Second’ WHEN 4 THEN ‘Third’ WHEN 8 THEN ‘Fourth’ WHEN 16 THEN ‘Last’ END + CASE [freq_interval] WHEN 1 THEN ‘ Sunday’ WHEN 2 THEN ‘ Monday’ WHEN 3 THEN ‘ Tuesday’ WHEN 4 THEN ‘ Wednesday’ WHEN 5 THEN ‘ Thursday’ WHEN 6 THEN ‘ Friday’ WHEN 7 THEN ‘ Saturday’ WHEN 8 THEN ‘ Day’ WHEN 9 THEN ‘ Weekday’ WHEN 10 THEN ‘ Weekend Day’ END + ‘ of every ‘ + CONVERT(VARCHAR, [freq_recurrence_factor]) + ‘ month(s)’ ELSE ” END + ‘ ‘+ CASE [freq_subday_type] WHEN 1 THEN ‘at ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) WHEN 2 THEN ‘ every ‘ + CONVERT(VARCHAR, [freq_subday_interval]) + ‘ Seconds(s) between ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) + ‘ and ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_end_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) WHEN 4 THEN ‘ every ‘ + CONVERT(VARCHAR, [freq_subday_interval]) + ‘ Minute(s) between ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) + ‘ and ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_end_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) WHEN 8 THEN ‘ every ‘ + CONVERT(VARCHAR, [freq_subday_interval]) + ‘ Hour(s) between ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) + ‘ and ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_end_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) ELSE ” END as varchar(max) ))
FROM INSERTED LEFT JOIN [sysschedules] on INSERTED.[schedule_id] = [sysschedules].[schedule_id]
SELECT @NewJobName = [sysjobs].name FROM INSERTED LEFT JOIN [sysjobs] ON INSERTED.[job_id] = [sysjobs].[job_id]
IF @NewScheduleName is not null
BEGIN
INSERT INTO [AuditDB].[audit].[SQLJobModificationHistory]
([EventTime]
,[EventType]
,[Message]
,[HostName]
,[Instance]
,[JobID]
,[JobName]
,[OldJobName]
,[JobCreationDate]
,[JobModificationDate]
,[AuditUser]
,[SessionLoginName])
VALUES
(GETDATE()
,’SCHEDULE_CREATED’
,CAST ( ‘User “‘+@UserName+’” has created job schedule “‘
+@NewScheduleName+’” of SQL Job “‘+@NewJobName+’” from host “‘+@HostName +’” at ‘
+CONVERT(VARCHAR(20),GETDATE(),100) +’ New frequency is “‘+@NewFrequency+’”‘ AS varchar(max))
,@HostName
,@SQLInstance
,@NewJobID
,@NewJobName
,’– Not Applicable –’
,@DateJobCreated
,@DateJobModified
,@UserName
,@SessionLogin)
END

COMMIT TRANSACTION;
END TRY

DECLARE @ErrorMessage [nvarchar](4000);
DECLARE @ErrorSeverity [int];
DECLARE @ErrorState [int];

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

— RAISERROR inside the CATCH block to return error
— information about the original error that caused
— execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, — Message text.
@ErrorSeverity, — Severity.
@ErrorState ); — State.
END CATCH
END
GO
— =============================================
— Author: <Wylie Blanchard>
— Create date: <2016-02-15>
— Description: –Create Trigger AuditDeleteSQLAgentJobScheduleTrigger
— =============================================
USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[AuditDeleteSQLAgentJobScheduleTrigger]
ON [msdb].[dbo].[sysjobschedules]
FOR DELETE

AS BEGIN

BEGIN TRY

BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE
@UserName [varchar](256)
,@SessionLogin [varchar](128)
,@HostName [varchar](128)
,@OldJobID [varchar](256)
,@OldJobName [varchar](256)
,@SQLInstance [varchar](128)
,@DateJobCreated [datetime]
,@DateJobModified [datetime]
,@OldScheduleName [varchar] (128)
,@OldFrequency [varchar](MAX)
,@OldScheduleID [int]
SELECT @UserName = SYSTEM_USER
SELECT @SessionLogin = ORIGINAL_LOGIN()
SELECT @HostName = HOST_NAME()
SELECT @OldJobID = [job_id] FROM DELETED
—–
SELECT @SQLInstance = CONVERT([varchar](128), SERVERPROPERTY(‘ServerName’))
SELECT @DateJobCreated = [sysjobs].[date_created] FROM DELETED LEFT JOIN [sysjobs] ON DELETED.[job_id] = [sysjobs].[job_id]
SELECT @DateJobModified = [sysjobs].[date_modified] FROM DELETED LEFT JOIN [sysjobs] ON DELETED.[job_id] = [sysjobs].[job_id]
SELECT @OldScheduleName = [name] FROM DELETED LEFT JOIN [sysschedules] on DELETED.[schedule_id] = [sysschedules].[schedule_id]
SELECT @OldFrequency = RTRIM(CAST ( CASE [freq_type] WHEN 1 THEN ‘Occurs Only Once’ WHEN 4 THEN ‘Occurs Every ‘ + CONVERT(VARCHAR, [freq_interval]) + ‘ day(s)’ WHEN 8 THEN ‘Occurs Every ‘ + CONVERT(VARCHAR, [freq_recurrence_factor]) + ‘ weeks(s) on ‘ + LEFT( CASE WHEN [freq_interval] & 1 = 1 THEN ‘Sunday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 2 = 2 THEN ‘Monday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 4 = 4 THEN ‘Tuesday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 8 = 8 THEN ‘Wednesday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 16 = 16 THEN ‘Thursday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 32 = 32 THEN ‘Friday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 64 = 64 THEN ‘Saturday, ‘ ELSE ” END , LEN( CASE WHEN [freq_interval] & 1 = 1 THEN ‘Sunday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 2 = 2 THEN ‘Monday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 4 = 4 THEN ‘Tuesday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 8 = 8 THEN ‘Wednesday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 16 = 16 THEN ‘Thursday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 32 = 32 THEN ‘Friday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 64 = 64 THEN ‘Saturday, ‘ ELSE ” END ) – 1 ) WHEN 16 THEN ‘Occurs Day ‘ + CONVERT(VARCHAR, [freq_interval]) + ‘ of every ‘ + CONVERT(VARCHAR, [freq_recurrence_factor]) + ‘ month(s)’ WHEN 32 THEN ‘Occurs on the ‘ + CASE [freq_relative_interval] WHEN 1 THEN ‘First’ WHEN 2 THEN ‘Second’ WHEN 4 THEN ‘Third’ WHEN 8 THEN ‘Fourth’ WHEN 16 THEN ‘Last’ END + CASE [freq_interval] WHEN 1 THEN ‘ Sunday’ WHEN 2 THEN ‘ Monday’ WHEN 3 THEN ‘ Tuesday’ WHEN 4 THEN ‘ Wednesday’ WHEN 5 THEN ‘ Thursday’ WHEN 6 THEN ‘ Friday’ WHEN 7 THEN ‘ Saturday’ WHEN 8 THEN ‘ Day’ WHEN 9 THEN ‘ Weekday’ WHEN 10 THEN ‘ Weekend Day’ END + ‘ of every ‘ + CONVERT(VARCHAR, [freq_recurrence_factor]) + ‘ month(s)’ ELSE ” END + ‘ ‘+ CASE [freq_subday_type] WHEN 1 THEN ‘at ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) WHEN 2 THEN ‘ every ‘ + CONVERT(VARCHAR, [freq_subday_interval]) + ‘ Seconds(s) between ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) + ‘ and ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_end_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) WHEN 4 THEN ‘ every ‘ + CONVERT(VARCHAR, [freq_subday_interval]) + ‘ Minute(s) between ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) + ‘ and ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_end_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) WHEN 8 THEN ‘ every ‘ + CONVERT(VARCHAR, [freq_subday_interval]) + ‘ Hour(s) between ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) + ‘ and ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_end_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) ELSE ” END as varchar(max) ))
FROM DELETED LEFT JOIN [sysschedules] on DELETED.[schedule_id] = [sysschedules].[schedule_id]
SELECT @OldJobName = [sysjobs].name FROM DELETED LEFT JOIN [sysjobs] ON DELETED.[job_id] = [sysjobs].[job_id]
IF @OldScheduleName is not null
BEGIN
INSERT INTO [AuditDB].[audit].[SQLJobModificationHistory]
([EventTime]
,[EventType]
,[Message]
,[HostName]
,[Instance]
,[JobID]
,[JobName]
,[OldJobName]
,[JobCreationDate]
,[JobModificationDate]
,[AuditUser]
,[SessionLoginName])
VALUES
(GETDATE()
,’SCHEDULE_DELETED’
,CAST ( ‘User “‘+@UserName+’” has deleted job schedule “‘
+@OldScheduleName+’” of SQL Job “‘+@OldJobName+’” from host “‘+@HostName +’” at ‘
+CONVERT(VARCHAR(20),GETDATE(),100) +’ Old frequency is “‘+@OldFrequency+’”‘ AS varchar(max))
,@HostName
,@SQLInstance
,@OldJobID
,@OldJobName
,’– Not Applicable –’
,@DateJobCreated
,@DateJobModified
,@UserName
,@SessionLogin)
END

COMMIT TRANSACTION;
END TRY

DECLARE @ErrorMessage [nvarchar](4000);
DECLARE @ErrorSeverity [int];
DECLARE @ErrorState [int];

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

— =============================================
— Author: <Wylie Blanchard>
— Create date: <2016-02-15>
— Description: –Create Trigger AuditUpdateSQLAgentJobScheduleTrigger
— =============================================
USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[AuditUpdateSQLAgentJobScheduleTrigger]
ON [msdb].[dbo].[sysschedules]
FOR UPDATE

AS BEGIN

BEGIN TRY

BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE
@UserName [varchar](256)
,@SessionLogin [varchar](128)
,@HostName [varchar](128)
,@NewJobID [varchar](256)
,@OldJobID [varchar](256)
,@NewJobName [varchar](256)
,@OldJobName [varchar](256)
,@SQLInstance [varchar](128)
,@DateJobCreated [datetime]
,@DateJobModified [datetime]
,@NewScheduleName [varchar] (128)
,@OldScheduleName [varchar] (128)
,@NewFrequency [varchar](MAX)
,@OldFrequency [varchar](MAX)
SELECT @UserName = SYSTEM_USER
SELECT @SessionLogin = ORIGINAL_LOGIN()
SELECT @HostName = HOST_NAME()
SELECT @NewJobID = [job_id] FROM INSERTED LEFT JOIN [sysjobschedules] ON INSERTED.[schedule_id]= [sysjobschedules].[schedule_id]
SELECT @OldJobID = [job_id] FROM DELETED LEFT JOIN [sysjobschedules] ON DELETED.[schedule_id]= [sysjobschedules].[schedule_id]
SELECT @SQLInstance = CONVERT([varchar](128), SERVERPROPERTY(‘ServerName’))
SELECT @DateJobCreated = [sysjobs].[date_created] FROM INSERTED LEFT JOIN [sysjobschedules] ON INSERTED.[schedule_id]= [sysjobschedules].[schedule_id] INNER JOIN [sysjobs] ON [sysjobschedules].[job_id] = [sysjobs].[job_id]
SELECT @DateJobModified = [sysjobs].[date_modified] FROM INSERTED LEFT JOIN [sysjobschedules] ON INSERTED.[schedule_id]= [sysjobschedules].[schedule_id] INNER JOIN [sysjobs] ON [sysjobschedules].[job_id] = [sysjobs].[job_id]
SELECT @NewScheduleName = [name] FROM INSERTED
SELECT @OldScheduleName = [name] FROM DELETED
SELECT @NewFrequency = RTRIM(CAST ( CASE [freq_type] WHEN 1 THEN ‘Occurs Only Once’ WHEN 4 THEN ‘Occurs Every ‘ + CONVERT(VARCHAR, [freq_interval]) + ‘ day(s)’ WHEN 8 THEN ‘Occurs Every ‘ + CONVERT(VARCHAR, [freq_recurrence_factor]) + ‘ weeks(s) on ‘ + LEFT( CASE WHEN [freq_interval] & 1 = 1 THEN ‘Sunday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 2 = 2 THEN ‘Monday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 4 = 4 THEN ‘Tuesday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 8 = 8 THEN ‘Wednesday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 16 = 16 THEN ‘Thursday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 32 = 32 THEN ‘Friday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 64 = 64 THEN ‘Saturday, ‘ ELSE ” END , LEN( CASE WHEN [freq_interval] & 1 = 1 THEN ‘Sunday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 2 = 2 THEN ‘Monday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 4 = 4 THEN ‘Tuesday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 8 = 8 THEN ‘Wednesday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 16 = 16 THEN ‘Thursday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 32 = 32 THEN ‘Friday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 64 = 64 THEN ‘Saturday, ‘ ELSE ” END ) – 1 ) WHEN 16 THEN ‘Occurs Day ‘ + CONVERT(VARCHAR, [freq_interval]) + ‘ of every ‘ + CONVERT(VARCHAR, [freq_recurrence_factor]) + ‘ month(s)’ WHEN 32 THEN ‘Occurs on the ‘ + CASE [freq_relative_interval] WHEN 1 THEN ‘First’ WHEN 2 THEN ‘Second’ WHEN 4 THEN ‘Third’ WHEN 8 THEN ‘Fourth’ WHEN 16 THEN ‘Last’ END + CASE [freq_interval] WHEN 1 THEN ‘ Sunday’ WHEN 2 THEN ‘ Monday’ WHEN 3 THEN ‘ Tuesday’ WHEN 4 THEN ‘ Wednesday’ WHEN 5 THEN ‘ Thursday’ WHEN 6 THEN ‘ Friday’ WHEN 7 THEN ‘ Saturday’ WHEN 8 THEN ‘ Day’ WHEN 9 THEN ‘ Weekday’ WHEN 10 THEN ‘ Weekend Day’ END + ‘ of every ‘ + CONVERT(VARCHAR, [freq_recurrence_factor]) + ‘ month(s)’ ELSE ” END + ‘ ‘+ CASE [freq_subday_type] WHEN 1 THEN ‘at ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) WHEN 2 THEN ‘ every ‘ + CONVERT(VARCHAR, [freq_subday_interval]) + ‘ Seconds(s) between ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) + ‘ and ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_end_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) WHEN 4 THEN ‘ every ‘ + CONVERT(VARCHAR, [freq_subday_interval]) + ‘ Minute(s) between ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) + ‘ and ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_end_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) WHEN 8 THEN ‘ every ‘ + CONVERT(VARCHAR, [freq_subday_interval]) + ‘ Hour(s) between ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) + ‘ and ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_end_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) ELSE ” END as varchar(max) ))
FROM INSERTED
SELECT @OldFrequency = RTRIM(CAST ( CASE [freq_type] WHEN 1 THEN ‘Occurs Only Once’ WHEN 4 THEN ‘Occurs Every ‘ + CONVERT(VARCHAR, [freq_interval]) + ‘ day(s)’ WHEN 8 THEN ‘Occurs Every ‘ + CONVERT(VARCHAR, [freq_recurrence_factor]) + ‘ weeks(s) on ‘ + LEFT( CASE WHEN [freq_interval] & 1 = 1 THEN ‘Sunday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 2 = 2 THEN ‘Monday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 4 = 4 THEN ‘Tuesday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 8 = 8 THEN ‘Wednesday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 16 = 16 THEN ‘Thursday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 32 = 32 THEN ‘Friday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 64 = 64 THEN ‘Saturday, ‘ ELSE ” END , LEN( CASE WHEN [freq_interval] & 1 = 1 THEN ‘Sunday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 2 = 2 THEN ‘Monday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 4 = 4 THEN ‘Tuesday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 8 = 8 THEN ‘Wednesday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 16 = 16 THEN ‘Thursday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 32 = 32 THEN ‘Friday, ‘ ELSE ” END + CASE WHEN [freq_interval] & 64 = 64 THEN ‘Saturday, ‘ ELSE ” END ) – 1 ) WHEN 16 THEN ‘Occurs Day ‘ + CONVERT(VARCHAR, [freq_interval]) + ‘ of every ‘ + CONVERT(VARCHAR, [freq_recurrence_factor]) + ‘ month(s)’ WHEN 32 THEN ‘Occurs on the ‘ + CASE [freq_relative_interval] WHEN 1 THEN ‘First’ WHEN 2 THEN ‘Second’ WHEN 4 THEN ‘Third’ WHEN 8 THEN ‘Fourth’ WHEN 16 THEN ‘Last’ END + CASE [freq_interval] WHEN 1 THEN ‘ Sunday’ WHEN 2 THEN ‘ Monday’ WHEN 3 THEN ‘ Tuesday’ WHEN 4 THEN ‘ Wednesday’ WHEN 5 THEN ‘ Thursday’ WHEN 6 THEN ‘ Friday’ WHEN 7 THEN ‘ Saturday’ WHEN 8 THEN ‘ Day’ WHEN 9 THEN ‘ Weekday’ WHEN 10 THEN ‘ Weekend Day’ END + ‘ of every ‘ + CONVERT(VARCHAR, [freq_recurrence_factor]) + ‘ month(s)’ ELSE ” END + ‘ ‘+ CASE [freq_subday_type] WHEN 1 THEN ‘at ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) WHEN 2 THEN ‘ every ‘ + CONVERT(VARCHAR, [freq_subday_interval]) + ‘ Seconds(s) between ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) + ‘ and ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_end_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) WHEN 4 THEN ‘ every ‘ + CONVERT(VARCHAR, [freq_subday_interval]) + ‘ Minute(s) between ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) + ‘ and ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_end_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) WHEN 8 THEN ‘ every ‘ + CONVERT(VARCHAR, [freq_subday_interval]) + ‘ Hour(s) between ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) + ‘ and ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [active_end_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) ELSE ” END as varchar(max) ))
FROM DELETED
SELECT @NewJobName = [sysjobs].name FROM INSERTED LEFT JOIN [sysjobschedules] ON INSERTED.[schedule_id]= [sysjobschedules].[schedule_id] INNER JOIN [sysjobs] ON [sysjobschedules].[job_id] = [sysjobs].[job_id]
SELECT @OldJobName = [sysjobs].name FROM DELETED LEFT JOIN [sysjobschedules] ON DELETED.[schedule_id]= [sysjobschedules].[schedule_id] INNER JOIN [sysjobs] ON [sysjobschedules].[job_id] = [sysjobs].[job_id]
IF (@NewScheduleName is not null and @OldScheduleName is not null) and (@NewScheduleName <> @OldScheduleName) and (@NewFrequency = @OldFrequency) –Change to Schedule Name but no change to Frequency
BEGIN
INSERT INTO [AuditDB].[audit].[SQLJobModificationHistory]
([EventTime]
,[EventType]
,[Message]
,[HostName]
,[Instance]
,[JobID]
,[JobName]
,[OldJobName]
,[JobCreationDate]
,[JobModificationDate]
,[AuditUser]
,[SessionLoginName])
VALUES
(GETDATE()
,’SCHEDULE_MODIFIED’
,CAST ( ‘User “‘+@UserName+’” has modified job schedule “‘+@OldScheduleName+’” with “‘
+@NewScheduleName+’” of SQL Job “‘+@OldJobName+’” from host “‘+@HostName+’” at ‘
+CONVERT(VARCHAR(20),GETDATE(),100) AS varchar(max))
,@HostName
,@SQLInstance
,@OldJobID
,@NewJobName
,@OldJobName
,@DateJobCreated
,@DateJobModified
,@UserName
,@SessionLogin)
END

IF (@NewScheduleName is not null and @OldScheduleName is not null) and (@NewScheduleName = @OldScheduleName) and ( @NewFrequency is not null and @OldFrequency is null) –No Change to Schedule Name but a new Frequency is added when it was previouslly null
BEGIN
INSERT INTO [AuditDB].[audit].[SQLJobModificationHistory]
([EventTime]
,[EventType]
,[Message]
,[HostName]
,[Instance]
,[JobID]
,[JobName]
,[OldJobName]
,[JobCreationDate]
,[JobModificationDate]
,[AuditUser]
,[SessionLoginName])
VALUES
(GETDATE()
,’SCHEDULE_MODIFIED’
,CAST ( ‘User “‘+@UserName+’” has modified job schedule “‘
+@OldScheduleName+’” of SQL Job “‘+@OldJobName+’” from host “‘+@HostName+’” at ‘
+CONVERT(VARCHAR(20),GETDATE(),100) +’ Old frequency is “‘+@OldFrequency+’”‘ AS varchar(max))
,@HostName
,@SQLInstance
,@OldJobID
,@NewJobName
,@OldJobName
,@DateJobCreated
,@DateJobModified
,@UserName
,@SessionLogin)
END

IF (@NewScheduleName is not null and @OldScheduleName is not null) and (@NewScheduleName = @OldScheduleName) and ( @NewFrequency is not null and @OldFrequency is not null) and (@NewFrequency <> @OldFrequency) –No Change to Schedule Name but change to Frequency
BEGIN

INSERT INTO [AuditDB].[audit].[SQLJobModificationHistory]
([EventTime]
,[EventType]
,[Message]
,[HostName]
,[Instance]
,[JobID]
,[JobName]
,[OldJobName]
,[JobCreationDate]
,[JobModificationDate]
,[AuditUser]
,[SessionLoginName])
VALUES
(GETDATE()
,’SCHEDULE_MODIFIED’
,CAST ( ‘User “‘+@UserName+’” has modified job schedule “‘
+@OldScheduleName+’” of SQL Job “‘+@OldJobName+’” from host “‘+@HostName+’” at ‘
+CONVERT(VARCHAR(20),GETDATE(),100) +’ Old frequency is “‘+@OldFrequency+’”‘ AS varchar(max))
,@HostName
,@SQLInstance
,@OldJobID
,@NewJobName
,@OldJobName
,@DateJobCreated
,@DateJobModified
,@UserName
,@SessionLogin)
End

IF (@NewScheduleName is not null and @OldScheduleName is not null) and (@NewScheduleName <> @OldScheduleName) and (@NewFrequency <> @OldFrequency) –Change to Schedule Name and change to Frequency
BEGIN
INSERT INTO [AuditDB].[audit].[SQLJobModificationHistory]
([EventTime]
,[EventType]
,[Message]
,[HostName]
,[Instance]
,[JobID]
,[JobName]
,[OldJobName]
,[JobCreationDate]
,[JobModificationDate]
,[AuditUser]
,[SessionLoginName])
VALUES
(GETDATE()
,’SCHEDULE_MODIFIED’
,CAST ( ‘User “‘+@UserName+’” has modified job schedule “‘+@OldScheduleName+’” with “‘
+@NewScheduleName+’” of SQL Job “‘+@OldJobName+’” from host “‘+@HostName+’” at ‘
+CONVERT(VARCHAR(20),GETDATE(),100) +’ Old frequency is “‘+@OldFrequency+’”‘ AS varchar(max))
,@HostName
,@SQLInstance
,@OldJobID
,@NewJobName
,@OldJobName
,@DateJobCreated
,@DateJobModified
,@UserName
,@SessionLogin)
END

COMMIT TRANSACTION;
END TRY

DECLARE @ErrorMessage [nvarchar](4000);
DECLARE @ErrorSeverity [int];
DECLARE @ErrorState [int];

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

GO
— =============================================
— Author: <Wylie Blanchard>
— Create date: <2016-02-15>
— Description: –Create Trigger AuditUpdateSQLAgentJobScheduleActivationTrigger
— =============================================
USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[AuditUpdateSQLAgentJobScheduleActivationTrigger]
ON [msdb].[dbo].[sysschedules]
FOR UPDATE

AS BEGIN

BEGIN TRY

— check if the enabled flag has been updated.
IF @NewEnabled <> @OldEnabled
BEGIN
IF @NewEnabled = 1
BEGIN
INSERT INTO [AuditDB].[audit].[SQLJobModificationHistory]
([EventTime]
,[EventType]
,[Message]
,[HostName]
,[Instance]
,[JobID]
,[JobName]
,[OldJobName]
,[JobCreationDate]
,[JobModificationDate]
,[AuditUser]
,[SessionLoginName])
VALUES
(GETDATE()
,’SCHEDULE_ENABLED’
,CAST ( ‘User “‘+@UserName+’” has enabled job schedule “‘+@NewScheduleName+’” from host “‘+@HostName+’” at ‘
+CONVERT(VARCHAR(20),GETDATE(),100) AS varchar(max))
,@HostName
,@SQLInstance
,@OldJobID
,@NewJobName
,@OldJobName
,@DateJobCreated
,@DateJobModified
,@UserName
,@SessionLogin)
END — End of inner-IF block…
IF @NewEnabled = 0
BEGIN
INSERT INTO [AuditDB].[audit].[SQLJobModificationHistory]
([EventTime]
,[EventType]
,[Message]
,[HostName]
,[Instance]
,[JobID]
,[JobName]
,[OldJobName]
,[JobCreationDate]
,[JobModificationDate]
,[AuditUser]
,[SessionLoginName])
VALUES
(GETDATE()
,’SCHEDULE_DISABLED’
,CAST ( ‘User “‘+@UserName+’” has disabled job schedule “‘+@NewScheduleName+’” from host “‘+@HostName+’” at ‘
+CONVERT(VARCHAR(20),GETDATE(),100) AS varchar(max))
,@HostName
,@SQLInstance
,@OldJobID
,@NewJobName
,@OldJobName
,@DateJobCreated
,@DateJobModified
,@UserName
,@SessionLogin)
END — End of inner-IF block…
END — End of outer-IF block…

COMMIT TRANSACTION;
END TRY

DECLARE @ErrorMessage [nvarchar](4000);
DECLARE @ErrorSeverity [int];
DECLARE @ErrorState [int];

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();