SQL Server EXECUTE AS trouble

Question

I'm missing something while trying to make my stored procedure use EXECUTE AS. The stored procedure is reading data from source_db, aggregates it and stores result in target_db.

The sp itself is in target_db. I have a dedicated login and map it to users in both source_db and target_db for sp's owner (so there is a user app_agent in source_db and in target_db for login app_agent).

If I log in as app_agent, and execute

EXEC target_db.app_agent_schema.import_data

everything works fine. But if I change

ALTER PROCEDURE app_agent_schema.import_data WITH EXECUTE AS OWNER` (or `AS SELF`) 

and try executing it, it throws

The server principal "app_agent" is not able to access the database "source_db" under the current security context.

I'm using SQL Server 2008.

Could someone point out my error?

Thanks

Update After doing some research, I found that ALTER DATABASE target_db SET TRUSTWORTHY ON solves the problem, but that doesn't seem as the right solution to me...

• Jon Seigel: I think the answer is using the database-level cross-database ownership chain option. I was able to reproduce the error in your scenario, but there aren't enough details given to know if I reproduced it exactly... the CDOC option didn't work for me, but try it out and see if that does it.

Answer 1

This is explained in Extending Database Impersonation by Using EXECUTE AS. The EXECUTE AS context is trusted only in the current database and allowing it to spill over to other databases is a escalation of privilege attack vector.

There are two solutions, both described in the article linked above:

• the easy one is to mark the database TRUSTWORTHY: ALTER DATABASE [source_db] SET TRUSTWORTHY ON;. Although easy, is also dangerous in as it makes the dbo of source_db a de-facto sysadmin.

• the safe one is use code signing, see Call a procedure in another database for an example. This is more complex, but is 100% buletproff security.

Answer 2

Which user runs the ALTER PROCEDURE command? It might have set the Owner (self) access level to that user, not the one you intended.

• a1ex07: The same user who created procedure (app_agent). If I have procedure created by app_agent without execute as owner/self, log in as app_agent, SP executes properly. If I add EXECUTE AS SELF (again, the same user), and log in even as app_agent, I get ...is not able to access the database...