locked
Permissioning across three databases RRS feed

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


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

    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

    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.

    Tuesday, June 27, 2017 9:42 PM