1

Using SQL-Server 2019; I am trying to use sp_send_dbmail and have successfully sent test emails from SQL-Server so believe the configuration is fine.

A sproc runs in the execution context of a SQL-Login, which updates some tables in a database and then calls the sproc below which is supposed to send emails via msdb.dbo.sp_send_dbmail.

CREATE PROCEDURE [dbo].[sp_SendEmail]
    @emailAddress   AS NVARCHAR(MAX),
    @emailBody      AS NVARCHAR(MAX),
    @emailSubject   AS NVARCHAR(150),
    @attachmentPath AS NVARCHAR(255) = NULL,
    @emailId        AS INT OUTPUT
WITH EXECUTE AS N'Bob'
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @bSuccess AS INT = 0;

    EXECUTE @bSuccess = [msdb].[dbo].[sp_send_dbmail]
       @profile_name     = N'Profile',
       @recipients       = @emailAddress,
       @subject          = @emailSubject,
       @body             = @emailBody,
       @body_format      = N'HTML',
       @importance       = N'High',
       @file_attachments = @attachmentPath,
       @mailitem_id      = @emailId OUTPUT;
    
    RETURN @bSuccess;
END
GO

When I run the sproc I get the error:

Msg 229, Level 14, State 5, Procedure msdb.dbo.sp_send_dbmail, Line 1 [Batch Start Line 2] The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

The SQL-Login, Bob, has a user mapping to the msdb database using the default schema and is a member of the DatabaseMailUserRole role.

I've given Bob execute permission on the sp_send_dbmail securable, which seems like a duplication of what the DatabaseMailUserRole provides, but still get the error above.

I'd appreciate any insight you can throw at me.

  • Try enabling the trustworthy flag on the database where the stored procedure lives. Or - review how to create a certificate and have the procedure execute using a certificate. – Jeff Oct 11 '21 at 21:23
  • Thank-you, making the database trustworthy did the trick – BraveSirRobin Oct 12 '21 at 08:15

0 Answers0