Auditing - sp_send_dbmail from server-level triggers failed

Question

I have a server-level trigger to send a mail when a database is created. I have a few users in the db_creator server role. The trigger fails for these users because they don't have permissions to execute sp_send_dbmail. Is there a better option here than adding the logins to the DatabaseMailUserRole in MSDB or adding "WITH EXECUTE AS 'sa'" (or similar with a lower-privileged account) to the trigger?

CREATE TRIGGER [ddl_trg_Create_Database]
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @results varchar(max)
declare @subjectText varchar(max)
declare @databaseName VARCHAR(255)
SET @subjectText = 'New database ceated - ' + @@SERVERNAME + ' by ' + SUSER_SNAME() 
SET @results = 
 (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
SET @databaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)'))
EXEC msdb.dbo.sp_send_dbmail
 @recipients = '[email protected]',
 @body = @results,
 @subject = @subjectText,
 @exclude_query_output = 1
GO
ENABLE TRIGGER [ddl_trg_Create_Database] ON ALL SERVER
GO

• Aaron Bertrand: You could also have the trigger call a stored procedure that sends the mail using EXECUTE AS, then at least the exposure is limited to that procedure and not everything in the trigger.

Answer 1

Well, the ideal solution would be to implement module signing along the lines of:

1. Create a Certificate in master
2. Sign the DDL Trigger with the Certificate
3. Create that same Certificate in msdb
4. Create a User in msdb from that Certificate
5. Add that Certificate-based User to the DatabaseMailUserRole Database Role

Unfortunately, however, it seems that Database-scoped Triggers (DDL) and Server-scoped Triggers (DDL and Logon) cannot be signed (so says Microsoft in this MSDN Forum thread: Error Signing a DDL Trigger).

But, I did find that simply moving the EXEC msdb.dbo.sp_send_dbmail logic into a stored procedure in master allows this to work. Apparently, both master and msdb are both, by default, enabled for Cross-Database Ownership Chaining, and a stored procedure is Schema-scoped, unlike a DDL Trigger. So, as long as the stored procedure in master is in the dbo Schema, it should work. You will just need to GRANT EXECUTE on this stored procedure to those same folks who you made members of the dbcreator fixed server role.

I have created a stored procedure and modified the DDL trigger to call the stored procedure as follows. Please note that I changed the variable datatypes to NVARCHAR and sysname as all of the values are truly NVARCHAR.

Stored Procedure

USE [master];
GO
CREATE PROCEDURE dbo.SendDdlEmail
(
 @EventData XML
)
AS
SET NOCOUNT ON;
DECLARE @Results NVARCHAR(MAX),
 @SubjectText NVARCHAR(MAX),
 @DatabaseName sysname;
SET @SubjectText = N'New database created - ' + @@SERVERNAME + N' by ' + ORIGINAL_LOGIN();
SET @Results = 
 @EventData.value(N'(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', N'NVARCHAR(MAX)');
SET @DatabaseName = @EventData.value(N'(/EVENT_INSTANCE/DatabaseName)[1]', N'sysname');
EXEC msdb.dbo.sp_send_dbmail
 @recipients = '[email protected]',
 @body = @Results,
 @subject = @SubjectText,
 @exclude_query_output = 1;
GO
GRANT EXECUTE ON [dbo].[SendDdlEmail] TO [xxxxxxxxxxxxx];
GO

Trigger

CREATE TRIGGER [ddl_trg_Create_Database]
ON ALL SERVER
FOR CREATE_DATABASE
AS
SET NOCOUNT ON;
DECLARE @EventData XML;
SET @EventData = EVENTDATA();
EXEC [master].[dbo].[SendDdlEmail] @EventData;
GO

---

FYI: I just added a Microsoft Connect Suggestion requesting the ability to sign non-Schema-scoped Triggers:

Allow signing Database DDL Triggers and Server DDL and Logon Triggers - ADD SIGNATURE