Триггер SSISDB для отправки электронной почты из БД Mail в SQL Server 2012

Question

Проблема: я не могу выполнить sp_send_mail из триггера на объектах SSISDB.

Исходная информация: мы используем SSIS, развернутую в каталоге SQL Server, и пытаемся разместить триггер на [internal].[projects] таблица SSISDB для управления электронной почтой при развертывании. Мы не пользуемся услугами Team Foundation, так как не хотим, чтобы наше использование SSIS ограничивалось, поэтому мы пытаемся найти творческий подход.

У нас настроена почта базы данных, и мы работаем без проблем, кроме случаев, когда мы фактически пытаемся выполнить развертывание, которое запускает триггер.

Ошибка, которую мы получаем:

Не удалось развернуть проект. Исправьте проблемы и повторите попытку позже. Текущая транзакция не может быть зафиксирована и не может поддерживать операции, которые записывают в файл журнала. Откат транзакции.

Разрешение EXECUTE было отклонено для объекта 'sp_send_dbmail', базы данных 'msdb', схемы 'dbo'.

После исчерпывающего исследования я обнаружил, что учетная запись пользователя выполняется как "S-1-9-3-130423068-130423068-130423068-130423068" (идентификатор был изменен, но вы поняли идею). Я назначил всех возможных пользователей в системе, которые я могу видеть DatabaseMailUserRole так, чтобы они могли по электронной почте, однако, что я продолжаю сталкиваться, является ошибкой выше или этой:

Не удалось развернуть проект. Исправьте проблемы и повторите попытку позже. Текущая транзакция не может быть зафиксирована и не может поддерживать операции, которые записывают в файл журнала. Откат транзакции.

Основной сервер "1-9-3-130423068-130423068-130423068-130423068". не может получить доступ к базе данных "" в текущем контексте безопасности.

Недавно я узнал, что это пользователь без логина. У кого-нибудь есть указания или советы, как мы можем это сделать? Я прочитал кое-что между переполнением стека и другими местами в Google, что мы должны создать логин для этого SID, чтобы иметь возможность назначать разрешения. Любая помощь будет принята с благодарностью!

Триггер выполняется

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

Я бы попробовал что-нибудь попроще.

План Б. Посмотрите, сможете ли вы, чтобы ваш триггер вставлял строку в базу данных / таблицу. Может быть, называется Развертывания.

Затем вы можете запускать задание агента sql с интервалом, не превышающим каждую секунду, для просмотра этой таблицы и отправки электронного письма. Последнее, что происходит, это то, что запись помечается чем-то вроде emailsent = 1, где это 0 при первой вставке.

Это должно обеспечить необходимое разделение интересов. Если у вас все еще есть проблемы с запуском этой вставки из триггера в технически "системную" таблицу, вы можете воспользоваться планом C.

План C. Ваше задание агента запрашивает таблицу SSISDB и для каждой записи, которая еще не существует в развертываниях, вставляет запись в таблицу развертываний с emailsent = 0. (Получить только новые записи) Затем следуйте плану B для остальных.

Надеюсь это поможет.