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.