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.
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.