xliu avatar image
xliu asked

DDL rollback trigger not sending email

I'd like to implement a DDL rollback trigger to prevent databases from being dropped and also email a warning about the attempt (and potentially include scripts to actually drop it). The trigger below sends email fine when the ROLLBACK is disabled (so database does get dropped): USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [testtrigger] ON ALL SERVER FOR DROP_DATABASE AS BEGIN DECLARE @text varchar(300) SELECT @text = 'Dropping database ' + EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(30)') + ' was attempted by ' + EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(30)') + '.' --SELECT @login=EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(300)') EXEC msdb.dbo.sp_send_dbmail @recipients = ';', @subject = 'Dropping database was attempted', @body = @text, @importance = 'high'; --ROLLBACK END GO ENABLE TRIGGER [testtrigger] ON ALL SERVER GO However, when ROLLBACK is enabled, it not only rolls back the drop transaction, but also prevents the email from being sent. The message returned from executing the drop command indicates that the mail was queued: Mail (Id: 1669) queued. Msg 3609, Level 16, State 2, Line 34 The transaction ended in the trigger. The batch has been aborted. Checking on queued mail by executing 'SELECT * FROM msdb.dbo.sysmail_allitems', however, indicates that nothing has been queued. All other database mail DMVs return no indication of mail activity from the trigger. So, it seems that the trigger also rolls back the database mail processes, even though it says mail has been queued. Is this true? Does anyone know of a workaround so that both the rollback of database drop and sending email can be achieved by the trigger?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

can you put the rollback before the email but after EVENTDATA()?
2 Likes 2 ·
rollback before sending the email?
1 Like 1 ·
As KenJ suggests - capture the information you need, into variables, prior to the rollback, as variables are unaffected by a rollback
1 Like 1 ·
Thanks for the suggestion. The EVENTDATA() function must be called before the ROLLBACK, or it will return NULL.
0 Likes 0 ·

1 Answer

· avatar image answered
This is the DDL trigger I use to stop none sysadmins from doing anything on the servers. Prints a message back to the user and sends the email along with the T-SQL they tried to run CREATE TRIGGER ON ALL SERVER FOR DDL_EVENTS AS IF IS_SRVROLEMEMBER('sysadmin') = 0 BEGIN DECLARE @data XML; DECLARE @schema SYSNAME; DECLARE @object SYSNAME; DECLARE @eventType SYSNAME; DECLARE @user SYSNAME; DECLARE @mess NVARCHAR(MAX); DECLARE @dbname SYSNAME; SET @user = SUSER_NAME() SET @data = EVENTDATA(); SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'SYSNAME'); SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'); SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'); SET @dbname = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','SYSNAME'); PRINT 'Only SysAdmins can modify this server and it'+char(39)+'s databases' PRINT 'You'+char(39)+'r statement has been logged and emailed to the DBA team' ROLLBACK TRANSACTION EXECUTE AS LOGIN = 'mail' SET @mess = ' User = ' + ' ' + UPPER(CONVERT(SYSNAME, @user)) + '' + ' has tried to do the following DDL operation' + '
' + 'DDL Operation = ' + ' ' + @eventType + '' + '
' + 'Object = ' + ' ' + @dbname + '.' + CONVERT(SYSNAME, @schema) + '.' + CONVERT(SYSNAME, @object) + ''; EXECUTE MSDB.DBO.sp_send_dbmail @profile_name = , @recipients = '', @subject = 'Server level change detected and rolled back', @body = @mess, @body_format = 'HTML' REVERT END
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rolling back after the function call but before sending mail worked perfectly. Thanks everyone for the help. Greatly appreciate it!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.