About this 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 throwsThe 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 like the right solution to me...