SQL Server 2005 – How to monitor SQL Job steps modification

In sql server, To monitor any modification we have two option one is either enable trace on sql server to capture the changes and another option is by creating triggers on tables. On daily sql server health check report, we have to make sure each modification is listed. For that we plan to create triggers on sysjobs and sysjobsteps to identify any changes done in sql server jobs.

Now we plan to automate the process by using database mail feature of sql server 2005. By default it is disable in sql server to enable database mail features, Run the below query in master database.

sp_configure ‘advanced options’,1 — This will give you the list of advanced features in sql server
sp_configure ‘Database Mail XPs’,1 — This will enable database mail feature in sql server reconfigure

Now we have to configure database mail follow the link Database mail.
We have created database mail profile to automate the process of monitoring sql server jobs and job steps.

How to automate sql server jobs alert using database mail?

Job information is store in the sysjobs table and job step related information gets store in sysjobstep table in the msdb system database. Any modification in jobs or job step get recorded in the respective tables.

Using “Enabled” column in sysjobs table we can identify which jobs are disabled or enabled. Now we are going to create update triggers on sysjobs and sysjobstep tables to capture the modification. Below script will give you alert if any modification done on sql server job.
USE msdb

CREATE TRIGGER tr_sysjobsteps
ON sysjobsteps
FOR UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON

DECLARE @UserName VARCHAR(50),
@HostName VARCHAR(50),
@JobName VARCHAR(100),
@DeletedJobName VARCHAR(100),
@New_Name sysname,
@Old_Name sysname,
@stepname sysname,
@old_cmd varchar(1000),
@new_cmd varchar(1000),
@jobid uniqueidentifier,
@Bodytext VARCHAR(2000),
@SubjectText VARCHAR(200),
@Servername VARCHAR(50)

SELECT @UserName = SYSTEM_USER
SELECT @HostName = HOST_NAME()
SELECT @New_Name = step_name FROM Inserted
SELECT @Old_Name = step_name FROM Deleted
SELECT @New_cmd = command FROM Inserted
SELECT @Old_cmd = command FROM Deleted
SELECT @Jobid = job_id FROM Deleted
SELECT @Servername = @@servername
SELECT @jobname = name
FROM sysjobsteps a, sysjobs b
WHERE a.job_id = b.job_id
AND b.job_id = @jobid
IF @New_Name <> @Old_Name
BEGIN

IF @Old_Name is not null
BEGIN
SET @bodytext = ‘User: ‘+@username+’ has deleted job step [‘+@Old_Name+’]
of SQL Job [‘+@jobname+’] from ‘+@hostname+’at ‘+CONVERT(VARCHAR(20),GETDATE(),100)
SET @subjecttext = @Servername+’ : [‘+@jobname+
‘] has been updated at ‘+CONVERT(VARCHAR(20),GETDATE(),100)
END

IF @New_Name is not null
BEGIN

SET @bodytext = ‘User: ‘+@username+’ has modified job step [‘+@Old_Name+’] with
[‘+@New_Name+’] of SQL Job [‘+@jobname+’] from ‘+@hostname+’at ‘+CONVERT(VARCHAR(20),GETDATE(),100)

SET @subjecttext = @Servername+’ : [‘+@jobname+’] has been modified
at ‘+CONVERT(VARCHAR(20),GETDATE(),100)
END
END

IF @New_cmd <> @old_cmd
BEGIN

SET @bodytext = ‘User: ‘+@username+’ has modified job step [‘+@Old_Name+’]
of SQL Job [‘+@jobname+’] from ‘+@hostname+’at ‘
+CONVERT(VARCHAR(20),GETDATE(),100) +’ Old command is ‘+@old_cmd

SET @subjecttext = @Servername+’ : [‘+@jobname+
‘] has been modified at ‘+CONVERT(VARCHAR(20),GETDATE(),100)
END

SET @subjecttext = ‘SQL Job on ‘ + @subjecttext

— mail allert
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘DBAProfile’, — Valid database profile
@recipients = ‘xyz@abc.com’, — email address of the team
@body = @bodytext,
@subject = @subjecttext
END

Run the above script in test environment to find out whether its working fine. As per your requirement you can edit the script. After editing the script, now try to modify any  sql server job steps that will fire mail in your inbox with useful information.

Leave a Reply

  

  

  


5 − 1 =