SSISDB Trigger to email from DB Mail in SQL Server 2012

Question

Problem: I'm unable to execute sp_send_mail from a trigger on SSISDB objects.

Background: we use SSIS deployed to the SQL Server catalog and we're trying to place a trigger on the [internal].[projects] table of SSISDB in order to email management when a deployment happens. We do not use Team Foundation services as we don't want to incur the overheard with our limited use of SSIS so we're trying to come up with creative ways around it.

We have database mail configured and working with no issues, except when we actually attempt a deployment which fires the trigger.

The error we get is:

Failed to deploy the project. Fix the problems and try again later.:The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

After exhaustive research, I've found the user account its being executed as "S-1-9-3-130423068-130423068-130423068-130423068" (id was changed but you get the idea). I've assigned all possible users in the system I can see to the DatabaseMailUserRole so that they can e-mail however what I keep encountering is the error above or this one:

Failed to deploy the project. Fix the problems and try again later.:The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

The server principal "1-9-3-130423068-130423068-130423068-130423068." is not able to access the database "" under the current security context.

What I've learned more recently is this is a user without a login. Does anyone have any pointers or advice in how we can go about this? I've read somethings between stack overflow and other places on google, that we should create a login for that SID to be able to assign permissions. Any help would be greatly appreciated!

Trigger being executed

CREATE TRIGGER [internal].[package_deploy]
ON [internal].[projects]
FOR INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @body NVARCHAR(MAX) = N'';
    DECLARE @subject NVARCHAR(100) = N'';
    DECLARE @recipients NVARCHAR(100) = '';
    DECLARE @profile_name NVARCHAR(100) = '';
    DECLARE @rc INTEGER = 0;

    SELECT @body += CHAR(13) + CHAR(10) + name + ' Project was deployed on: ' + CAST(last_deployed_time AS NVARCHAR(MAX))
            + CHAR(13) + CHAR(10) + ' for ticket(s): ' + description
    FROM inserted;

    SELECT @subject = name + ' Project Deployment Notification' FROM inserted;

    SELECT @recipients = @recipients
    SELECT @profile_name = @profile_name

    IF EXISTS (SELECT 1 FROM inserted)
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail
          @recipients = @recipients, 
          @profile_name = @profile_name,
          @subject = @subject, 
          @body = @body;
    END
END
GO

Answer 1

I would try something simpler.

Plan B. See if you can have your trigger insert a row into a database / table. Maybe called Deployments.

Then you can have a sql agent job run on a interval, as short as every second, to look at that table and send the email. The last thing that happens is that record is marked with something like emailsent = 1 where it is 0 when first inserted.

This should provide the separation of concerns necessary. If you still have trouble running that insert from the trigger on what is technically a 'system' table then you can take plan C.

Plan C. Your agent job queries the SSISDB table and for each entry that does not already exist in deployments, inserts the record into your deployments table with emailsent = 0. (Get only new records) Then follow plan B for the rest.

Hope this helps.