Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to send database mail but I am getting EXECUTE permission denied on the object 'sp_send_dbmail' database 'msdb', schema 'dbo'.. I code I am running is as follows:

SELECT SUSER_NAME(), USER_NAME();
Create USER kyle_temp FOR LOGIN Foo
EXECUTE AS USER = 'kyle_temp';
SELECT SUSER_NAME(), USER_NAME();
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Mail Profile',
            @recipients = 'test@test.com',
            @subject = 'Test',
              @body = 'Test'
REVERT;
DROP USER kyle_temp

The Foo Login shows that it is mapped to the Foo User in msdb. When I look at the foo user in msdb I see that it has "DatabaseMailUserRole" Checked and has Execute on dbo sp_send_dbmail.

What am I missing?

share|improve this question
add comment

1 Answer

You are running into the dreaded 'sandboxed' mode of the EXECUTE AS context, as described in Extending Database Impersonation by Using EXECUTE AS. In short code running under EXECUTE AS USER ... is trusted only inside the database context, not at the instance context.

There are three ways out:

  • the easy way: mark the current database as TRUSTWORTHY ALTER DATABASE [...] SET TRUSTWORTHY ON;
  • the correct way out: use code signing
  • the cheat: use EXECUTE AS LOGIN

If in your environment the dbo of the current database is trusted then you can go with TRUSTWORTHY. It will work, but if this property is set then any db_owner in the current DB can elevate himself to server admin.

If you want a 'correct' solution then:

  1. move this code in an stored proc
  2. sign the stored proc with a certificate
  3. drop the private key (so that it can never be used again to sign anything)
  4. export the public key, import it in [msdb]
  5. create user in [msdb] derived from this certificate
  6. grant necessary permissions (AUTHENTICATE, EXECUTE on sp_send_mail) to the certificate derived user

Trivial, heh? BTW, every time you modify the signed stored proc the signature is lost and the procedure has to be repeated. See Call a procedure in another database from an activated procedure for an example.

I totally don't recommend using EXECUTE AS LOGIN instead.

share|improve this answer
    
In the long run I am trying to have this executed as a trigger, so my EXECUTE AS is a way of testing in the short term... –  Kyle Brandt Mar 22 '12 at 17:21
1  
The cleanest way is to have a local stored proc in your DB, and have the call to msdb.dbo.sp_send_mail in this local SP body. Have the trigger call this local SP. Code sign the SP. –  Remus Rusanu Mar 22 '12 at 17:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.