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?
Best 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 underEXECUTE AS USER ...
is trusted only inside the database context, not at the instance context.There are three ways out:
ALTER DATABASE [...] SET TRUSTWORTHY ON;
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 anydb_owner
in the current DB can elevate himself to server admin.If you want a 'correct' solution then:
[msdb]
[msdb]
derived from this certificateTrivial, 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.