mssql trigger sql-agent
- Category: 電腦相關
- Last Updated: Thursday, 05 January 2017 13:42
- Published: Wednesday, 07 September 2016 13:38
- Written by sam
公司幅員遼闊,且在安全方面限制尚無作為
致人員時常反應,Job被停掉了~~~
目前作法是在MSSQL Server的sysjobs建立一項Trigger
當有人停用或啟用工作排程時,會發信至設定的信箱
USE msdb GO CREATE TRIGGER tr_SysJobs_enabled_SAM ON sysjobs FOR UPDATE AS SET NOCOUNT ON DECLARE @UserName VARCHAR(50), @HostName VARCHAR(50), @JobName VARCHAR(100), @DeletedJobName VARCHAR(100), @New_Enabled INT, @Old_Enabled INT, @Bodytext VARCHAR(200), @SubjectText VARCHAR(200), @Servername VARCHAR(50) SELECT @UserName = SYSTEM_USER, @HostName = HOST_NAME() SELECT @New_Enabled = Enabled FROM Inserted SELECT @Old_Enabled = Enabled FROM Deleted SELECT @JobName = Name FROM Inserted SELECT @Servername = @@servername -- check if the enabled flag has been updated. IF @New_Enabled <> @Old_Enabled BEGIN IF @New_Enabled = 1 BEGIN SET @bodytext = 'User: '+@username+' from '+@hostname+ ' ENABLED SQL Job ['+@jobname+'] at '+CONVERT(VARCHAR(20),GETDATE(),100) SET @subjecttext = @Servername+' : ['+@jobname+ '] has been ENABLED at '+CONVERT(VARCHAR(20),GETDATE(),100) END IF @New_Enabled = 0 BEGIN SET @bodytext = 'User: '+@username+' from '+@hostname+ ' DISABLED SQL Job ['+@jobname+'] at '+CONVERT(VARCHAR(20),GETDATE(),100) SET @subjecttext = @Servername+' : ['+@jobname+ '] has been DISABLED at '+CONVERT(VARCHAR(20),GETDATE(),100) END SET @subjecttext = 'SQL Job on ' + @subjecttext -- send out alert email EXEC msdb.dbo.sp_send_dbmail @profile_name = 'sam', @recipients = 'sam小老鼠myla.gotdns.com', @body = @bodytext, @subject = @subjecttext END