This question is similar to but not the same as this.
I created a stored procedure and set it to execute under the context of a sysadmin login (let's called it sys1). For testing purposes, I only included an execute sp_send_dbmail statement in there:
create procedure temp_impersonation_test with execute as 'sys1'
as
exec msdb.dbo.sp_send_dbmail
@subject = 'test',
@body = 'test',
@recipients = '[email protected]'
Now here are some facts. Both sys1 and I are sysadmins on the server. Neither login has trouble running the standalone sp_send_dbmail statement. Neither login has trouble running the stored procedure when it is not set to execute under a different context.
However when the sp is set to execute under a different context, it fails with the following error. Note that this even happens when sys1 tries to execute it (effectively, it will try to impersonate itself).
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.
I have tried the usual things like re-attaching the database user to the server login for the current database context and for msdb (I actually had to create the user for msdb as it wasn't there before.), recreate the mail profile and putting both users in the databasemailuserrole in msdb.
I think it's worth it to note that this issue started happening after we moved to a new server with SQL 2008 R2. The code worked fine on the old server which ran SQL 2005.
I am baffled. How could this happen? Both accounts are sysadmins!
Update: I just tried this on some of our other database servers and this issue occurs on all of them regardless of the server version. I think there must be a database setting I've missed out somewhere!
