Is there a way to provide access to users in my database to execute msdb.dbo.sp_send_dbmail
without needing to add them to the MSDB database and the DatabaseMailUserRole?
I've tried this:
ALTER PROCEDURE [dbo].[_TestSendMail]
(
@To NVARCHAR(1000),
@Subject NVARCHAR(100),
@Body NVARCHAR(MAX)
)
WITH EXECUTE AS OWNER
AS
BEGIN
EXEC msdb.dbo.sp_send_dbmail @profile_name = N'myProfile',
@recipients = @To, @subject = @Subject, @body = @Body
END
But I get this error:
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.
Thanks!
See Question&Answers more detail:os