公司幅員遼闊，且在安全方面限制尚無作為

致人員時常反應，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

• Prev
• Next