Answered by:
Permissioning across three databases

Question
-
Scenario
CREATE DATABASE clientdb
CREATE DATABASE intermediate
CREATE DATABASE targetCREATE PROCEDURE intermediate.dbo.execremote WITH EXECUTE AS 'targetuser' AS EXECUTE target.dbo.remote
CREATE PROCEDURE target.dbo.remote AS SELECT DB_NAME()CREATE LOGIN targetuser
CREATE LOGIN clientuserUSE intermediate
CREATE USER targetuser FOR LOGIN targetuser
GRANT EXECUTE ON execremote TO targetuserUSE target
CREATE USER targetuser FOR LOGIN targetuser
CREATE USER clientuser FOR login clientuser
GRANT EXECUTE ON remote TO clientuserUSE clientdb
CREATE USER clientuser FOR login clientuserIssue
USE clientdb
EXECUTE AS LOGIN = 'clientuser'
EXEC intermediate.dbo.execremote
REVERTMsg 916, Level 14, State 1, Procedure execremote, Line 2
The server principal "targetuser" is not able to access the database "target" under the current security context.Question
The only way I've managed to make this work is to make the "intermediate" database trustworthy. Is this the right way to solve it or is there a better way? Trustworthy seems like a sledgehammer to crack a nut.
- Edited by lb483 Tuesday, June 27, 2017 6:56 PM Missed detail
Tuesday, June 27, 2017 4:14 PM
Answers
-
Dan has been kind to point you to my article, and I try to cover all possibilities for the case. What is the best choice depends on the circumstances. If it is only a matter of a few calls, certificate signing is likely to be best choice.
If there are lots of them, TRUSTWORTHY + EXECUTE AS can be the best choice, but an absolute requirement in such case is that the databases are owned by an SQL login with no server-level permission whatsoever. The database must absolutely not be owned by sa. Also, an assumption is that a person who is db_owner in the TRUSTWORTHY database, also is db_owner in the other database with the same owner.
- Proposed as answer by Lin LengMicrosoft contingent staff Wednesday, June 28, 2017 4:44 AM
- Marked as answer by lb483 Wednesday, June 28, 2017 5:00 AM
Tuesday, June 27, 2017 9:42 PM
All replies
-
What you are trying to do is called "cross database ownership chaining".
Please see:
https://www.mssqltips.com/sqlservertip/1782/understanding-cross-database-ownership-chaining-in-sql-server/
Tuesday, June 27, 2017 4:46 PM -
I did see that article and tried enabling database chaining on each of the three databases but it made no difference.Tuesday, June 27, 2017 4:57 PM
-
What is the reason for EXECUTE AS in the proc? The EXECUTE AS user is generally sandboxed to that database. If you remove EXECUTE AS and the databases have the same owner, you can enable DB_CHAINING in the databases and all should work fine without TRUSTWORTHY.
I suggest your peruse Erland's article for a thorough discussion on your options: http://www.sommarskog.se/grantperm.html#dbchainging
Dan Guzman, Data Platform MVP, http://www.dbdelta.com
Tuesday, June 27, 2017 5:07 PM -
Unfortunately without the EXECUTE AS, the calling user would need to be permissioned against the target database, which we don't want to do.
Note: I've just realised that requirement is not in the original scenario so I've added it.
Tuesday, June 27, 2017 6:54 PM -
Module signing eliminates the need for impersonation with EXECUTE AS, as detailed in the article link I provided. The caller only needs access to the database with the directly executed proc and execute permissions on the proc.
Dan Guzman, Data Platform MVP, http://www.dbdelta.com
- Proposed as answer by Lin LengMicrosoft contingent staff Wednesday, June 28, 2017 4:44 AM
Tuesday, June 27, 2017 9:09 PM -
Dan has been kind to point you to my article, and I try to cover all possibilities for the case. What is the best choice depends on the circumstances. If it is only a matter of a few calls, certificate signing is likely to be best choice.
If there are lots of them, TRUSTWORTHY + EXECUTE AS can be the best choice, but an absolute requirement in such case is that the databases are owned by an SQL login with no server-level permission whatsoever. The database must absolutely not be owned by sa. Also, an assumption is that a person who is db_owner in the TRUSTWORTHY database, also is db_owner in the other database with the same owner.
- Proposed as answer by Lin LengMicrosoft contingent staff Wednesday, June 28, 2017 4:44 AM
- Marked as answer by lb483 Wednesday, June 28, 2017 5:00 AM
Tuesday, June 27, 2017 9:42 PM