How to give access to SQL Server database mail procedures for a Login in a different database?

Question

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.

Answer 1

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.

Answer 2

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.