All we need is an easy explanation of the problem, so here it is.
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
The sp itself is in
target_db. I have a dedicated login and map it to users in both
target_db for sp’s owner (so there is a user
source_db and in
target_db for login
If I log in as
app_agent, and execute
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?
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…
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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
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.
Which user runs the ALTER PROCEDURE command? It might have set the Owner (self) access level to that user, not the one you intended.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂