Auditing - sp_send_dbmail from server-level triggers failed 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 = 'DBA_GROUP@SomeCompanyName.com',
 @body = @results,
 @subject = @subjectText,
 @exclude_query_output = 1
GO

ENABLE TRIGGER [ddl_trg_Create_Database] ON ALL SERVER
GO
Well, the ideal solution would be to implement module signing along the lines of:


Create a Certificate in master
Sign the DDL Trigger with the Certificate
Create that same Certificate in msdb
Create a User in msdb from that Certificate
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 = 'DBA_GROUP@SomeCompanyName.com',
  @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