I'd like to know how to set up permissions within SQL Server to allow my application Login/Role to be able to send email using msdb.dbo.sp_send_dbmail
.
I have a database MyDb, a user MyUser
who is a member of role AppRole
. I have a stored procedure myProc
that calls msdb.dbo.sp_send_dbmail
. If I execute myProc
while logged in as sa
it all works fine, but if I execute while logged in as MyUser
I get an error:
Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.
My database does not have TRUSTWORTHY ON, therefore I believe I can't use EXECUTE AS to impersonate a different user, e.g. create myProc with EXECUTE AS OWNER... (MSDN reference)
Therefore I think I need to make my user(s) also users within msdb, but can I do this at the Role level or do I need to make each of my database users also users in msdb?
The database mail profile that I'm using is set to public, so I don't think this is related to the profile permissions.
According to microsoft you just need to give the users permission to "the DatabaseMailUser database role in the msdb database".
you need give database Role-->databaseusermailuserRole
You should be able to just grant MyUser public access to MSDB and then grant execute access on the sp_send_dbmail proc to that user. You can add the user by themselves or create a custom role in MSDB and add all the users that need exec access to sp_send_dbmail.
You can use EXECUTE AS and sign your procedure and then use the signature certificate to grant EXECUTE permission in
msdb
. See Call a procedure in another database from an activated procedure, as well as Signing Procedures with Certificates.