Ask a Question
Ask Question Login
Corporate Training
  1. Community
  2. SQL Server
  3. Question
SQL Server

How can I solve SQL Server EXECUTE AS trouble?

Asked by Ankesh Kumar Apr 22, 2021 1.3K views 1 answer
Share

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...




Your answer

1 Answer

More SQL Server discussions

Learn & Explore

Free tutorials and interview questions from industry experts — learn the skill, then get ready to prove it.

Latest SQL Server Blogs

Guides, tips and career advice on SQL Server from JanBask experts.