At first, it seemed the most obvious technology to use for the project goal was a DDL Trigger. I had recently re-read Robyn Page’s article on DDL Triggers and their usage, and was keen to try this approach. However, a problem with the EVENTDATA function surfaced immediately. Based on examination of BOL it appeared that If the ALTER TABLE command were used to disable / enable a DML trigger the intended information could be captured. But if the DISABLE TRIGGER, ENABLE TRIGGER, or the GUI of the Management Studio, were used to change a trigger’s status, the information would not be collected.
A quick test using the Studio’s GUI and a prototype DDL trigger proved this assumption correct. After a few days of prowling Red Gate and Microsoft forums I gathered from various posts that in point of fact, there is no way to capture information about an ENABLE TRIGGER or a DISABLE TRIGGER command in EVENTDATA. (Since this was the case, Event Notification was also out of the question).
(This assumption about EVENTDATA may be incorrect, but the need was pressing, the facts available supported this conclusion, and so I decided to move on.)
Since EVENTDATA appears to be useless to the task at hand, the two mechanisms that seemed to meet the pro-active notification requirement – DDL Trigger and Event Notification - were out of the running. We’ve been using the DBMail functionality here for some time. We’ve found it to be 100% reliable to date.
The concept of a job scheduled to ‘spin’ every minute was settled on. If the overhead were low, it should not burden the system. If a trigger were disabled and re-enabled before the job could spin and detect this, then “no harm, no foul”. Well actually there is a small window of risk, but this was deemed acceptable for our environment.
So, the current (for the present) solution to the problem of getting g notified about a DML trigger status change is to schedule a TSQL job to run every minute, 24x7, and if the status of a trigger has changed in the last minute, log to a current status table and send an email.
USE [DBA] -- my database for all audit tables on the system
GO
/****** Object: Table [dbo].[tbl_triggerAuditRpt] */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_triggerAuditRpt](
[auditDate] [datetime] NOT NULL,
[triggerName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[is_disabled] [bit] NOT NULL,
[triggerStatus] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[trigger_table] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[who_changed] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[userName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[suserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[originalLogin] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE dba -- my database for all audit tables on the system
;
IF EXISTS(SELECT * FROM sysobjects WHERE NAME = 'tbl_triggerAuditFires' AND TYPE = 'u')
DROP TABLE tbl_triggerAuditFires -- checks for status change in triggers
;
USE <snip> -- name of ERP database, put your database to monitor here
;
SELECT GETDATE() AS auditDate, sys.triggers.NAME AS triggerName, sys.triggers.is_disabled,
CASE WHEN sys.triggers.is_disabled = '1' THEN 'Disabled' ELSE 'Enabled'
END AS triggerStatus,
sysobjects.NAME AS trigger_table, dt.who_changed,
(SELECT (CONVERT([nvarchar](50),user_name(),(0))) ) AS [userName],
(SELECT (CONVERT([nvarchar](50),suser_sname(),(0))) ) AS [suserName],
(SELECT (CONVERT([nvarchar](50),original_login(),(0)))) AS [originalLogin]
INTO dba.dbo.tbl_triggerAuditFires
FROM
-- if/when user process making change is using ERP login ‘psqladm’ get the real username
(SELECT CASE WHEN SESSION_USER = 'psqladm' THEN
(SELECT cast(SUBSTRING( b.HOST_NAME,2,(CHARINDEX('>',b.HOST_NAME)-2) )
AS nvarchar(50)) AS who_changed
FROM sys.sql_logins a
INNER JOIN sys.dm_exec_sessions b
ON a.sid = b.security_id
WHERE a.NAME = 'psqladm'
AND b.session_id = (select @@SPID))
WHEN SESSION_USER <> 'psqladm' THEN
(SELECT CAST(SESSION_USER as NVARCHAR(50)))
END as who_changed) as dt,
sys.triggers
LEFT OUTER JOIN sysobjects
ON sys.triggers.parent_id = sysobjects.id
;
USE dba
;
IF EXISTS (SELECT * FROM sysobjects WHERE NAME = 'tbl_triggerChangeDecision' AND TYPE = 'u')
DROP TABLE tbl_triggerChangeDecision -- for mailing status changed rows
;
SELECT a.*
INTO dba.dbo.tbl_triggerChangeDecision
FROM dba.dbo.tbl_triggerAuditFires a -- checks for status change
INNER JOIN dba.dbo.tbl_triggerAuditRpt b -- holds current status of triggers
ON a.triggerName = b.triggerName
WHERE a.triggerStatus <> b.triggerStatus
;
-- if there has been a status change, then send alert mail to sysadmins
IF (SELECT COUNT(*) FROM dba.dbo.tbl_triggerChangeDecision) > 0
BEGIN
;
DECLARE @TRGRRCHNG NVARCHAR(MAX) ;
SET @TRGRRCHNG =
N'<font face="Calibri"><H1>Trigger Change Alert</H1>' +
N'<H2>The following trigger(s) had their status changed</H2>' +
N'<table border="1">' +
N'<tr><th>auditDate</th>' +
N'<th>Trigger Name</th>' +
N'<th>NEW Trigger Status</th>' +
N'<th>Trigger Parent Table</th>' +
N'<th>Who Changed</th>' +
N'<th>Original Login</th>' +
CAST( (
select td = auditDate , '',
td = triggerName , '',
td = triggerStatus , '',
td = trigger_Table , '',
td = who_changed , '',
td = originalLogin
from dba.dbo.tbl_triggerChangeDecision
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</font></table>'
;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'sqlmail',
-- your email addresses here
@recipients = '<sysadmin@email.com>; <sysadmin2@email.com>',
@subject = 'FROM SERVER03 TRIGGER ALERT !!! TRIGGER WAS CHANGED',
@body = @TRGRRCHNG ,
@body_format = 'HTML'
;
TRUNCATE TABLE dba.dbo.tbl_triggerAuditRpt -- empty out current status of triggers tbl
;
INSERT INTO dba.dbo.tbl_triggerAuditRpt -- add updated status of triggers as new current status
SELECT * FROM dba.dbo.tbl_triggerAuditFires
;
END
;
When the job fires and captures a change, this is what the system administrators receive in their inbox:
I do not claim that this approach is foolproof, but it is much better than what we had prior to this project. However, I have some criticisms to myself and responses to myself about the project in its current state: