Send Alert Email On Disabling DDL Trigger

Question

Post reply

Send Alert Email On Disabling DDL Trigger

RCRock

Right there with Babe

Points: 718
More actions
September 2, 2022 at 10:47 am

#4083223

Is there anyway to send alert email if someone disable the DDL trigger?
Alert notification should include detail like who did disable the trigger, when it was done, Trigger name etc
Please send me the script if possible

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Andrey SSC Eights! Points: 840 More actions · Answer 1

have a look here:

https://www.mssqltips.com/sqlservertip/5659/customize-sql-server-notifications-for-ddl-changes/

RCRock Right there with Babe Points: 718 More actions · Answer 2

Thanks but this is more related to create DDL trigger and send alert for any DB OBJECT CHANGE events like Create/Alter/Drop etc

I am looking to alert if someone disable the DDL trigger itself.

ratbak Ten Centuries Points: 1388 More actions · Answer 3

You can run a scheduled job to look for it w/ something like the following:

declare @recipientCSV varchar(8000)='recipients@yourcompany.com';
declare @subject varchar(1000)='ALERT - Trigger Missing or Disabled';
declare @text varchar(max)='Trigger ALERT on  Server ' + @@servername;

if not exists (select * from sys.server_triggers where name='TriggerName' and is_disabled=0)

begin
    exec msdb.dbo.sp_send_dbmail
        @recipients = @recipientCSV,
        @body = @html,
        @body_format = 'text',
        @subject = @subject;
    end

Jeff Moden SSC Guru Points: 1004474 More actions · Answer 4

We have such a system in place. It won't stand up in court, though because, me being a DBA with SysAdmin privs (or anyone else with SysAdmin privs) could turn off the monitoring, have our way with things, and then turn it back on.

IMHO, there would need to be some sort of external monitoring that would write to a file that virtually no one had access to but I'm not sure even that would stand up in court because someone always has access to anything.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)