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.

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 = 'james.jiao@someplace.com'

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!

share|improve this question
add comment

1 Answer 1

up vote 4 down vote accepted

When EXECUTE AS is used within a stored procedure to switch security contexts, it is sandboxed to the current database. This can be extended by using the database property TRUSTWORTHY or the server option cross database ownership chaining but there are security implications to those decisions.

share|improve this answer
    
That did turn out to be what went wrong. There is, however, another way, that is, to use certificates. It's much more secure than turning on TRUSTWORTHY. –  deutschZuid Nov 27 '12 at 2:50
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.