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)', 'varchar(30)') + ' was attempted by ' + EVENTDATA().value('(/EVENT_INSTANCE/LoginName)', 'varchar(30)') + '.' --SELECT @login=EVENTDATA().value('(/EVENT_INSTANCE/LoginName)', 'varchar(300)') EXEC msdb.dbo.sp_send_dbmail @recipients =
'email@example.com;', @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?
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)', 'SYSNAME'); SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)', 'SYSNAME'); SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)', 'SYSNAME'); SET @dbname = @data.value('(/EVENT_INSTANCE/DatabaseName)','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