Permissioning across three databases

Question

Scenario

CREATE DATABASE clientdb
CREATE DATABASE intermediate
CREATE DATABASE target

CREATE 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 clientuser

USE intermediate
CREATE USER targetuser FOR LOGIN targetuser
GRANT EXECUTE ON execremote TO targetuser

USE target
CREATE USER targetuser FOR LOGIN targetuser
CREATE USER clientuser FOR login clientuser
GRANT EXECUTE ON remote TO clientuser

USE clientdb
CREATE USER clientuser FOR login clientuser

Issue

USE clientdb
EXECUTE AS LOGIN = 'clientuser'
EXEC intermediate.dbo.execremote
REVERT

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

Answer 1

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/

Answer 2

I did see that article and tried enabling database chaining on each of the three databases but it made no difference.

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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.