When you create this trigger on the sysjobs table of msdb, it will send out an email alert whenever someone changes the state of the job from Enabled to Disabled or from Disabled state to Enabled.
For setting up database mail option and to use sp_send_dbmail, refer to my earlier post "Configuring Database Mail"
For setting up database mail option and to use sp_send_dbmail, refer to my earlier post "Configuring Database Mail"
USE msdb GO CREATE Trigger tr_AuditJobEnable ON sysjobs FOR UPDATE AS DECLARE @UserName VARCHAR(50), @HostName VARCHAR(50), @JobName VARCHAR(100), @DeletedJobName VARCHAR(100), @Ins_EnabledFlag INT, @Del_EnabledFlag INT, @Body VARCHAR(200), @Subject VARCHAR(200), @Servername VARCHAR(50) SELECT @UserName = SYSTEM_USER, @HostName = HOST_NAME() SELECT @Ins_EnabledFlag = Enabled FROM Inserted SELECT @Del_EnabledFlag = Enabled FROM Deleted SELECT @JobName = Name FROM Inserted SELECT @Servername = @@servername IF @Ins_EnabledFlag <> @Del_EnabledFlag BEGIN IF @Ins_EnabledFlag = 1 BEGIN SET @Body = 'The User "'+@username+'" from "'+@hostname+ '" ENABLED the Job "'+@jobname+'" on '+CONVERT(VARCHAR(20),GETDATE(),100) SET @Subject = 'SQL Job "'+@jobname+ '" on ' + @Servername+ ' has been ENABLED at '+CONVERT(VARCHAR(20),GETDATE(),100) END IF @Ins_EnabledFlag = 0 BEGIN SET @Body = 'The User "'+@username+'" from "'+@hostname+ '" DISABLED the Job "'+@jobname+'" on '+CONVERT(VARCHAR(20),GETDATE(),100) SET @Subject = 'SQL Job "'+@jobname+ '" on ' + @Servername+ ' has been DISABLED at '+CONVERT(VARCHAR(20),GETDATE(),100) END -- Send e-Mail Exec msdb..sp_send_dbmail @profile_name='DBA' -- Change to your Profile ,@recipients='segu.sandesh@gmail.com' -- Change Recipients ,@Subject=@Subject ,@Body=@Body END
1 comment:
can u post upgaration & migration procedure...with step by step
Post a Comment