sql-serversql-server-2005database-permissionsdatabase-mail

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


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.


Solution

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