locked
Cross database ownership chaining not working - sql server 2014 RRS feed

  • Question

  • Hi,

    I am trying to figure this out for a while and it is really annoying as it works on one server, and not the other.

    We are running one stored procedure, rpt.GetExtract, in one database (Datamart), and that stored procedure is referring dbo.LogStats table in the other database (Meta). Object owner of both objects is dbo in respective databases. Owner of both schemas, rpt and dbo, is dbo, in respective databases. When user runs this proc (which they have execute on through the database role), gets the following error:

    Msg 229, Level 14, State 5, Procedure GetExtract, Line 40
    The SELECT permission was denied on the object 'LogStats', database 'Meta', schema 'dbo'.

    1. Cross db ownership is enabled at the server level on both servers: sp_configure 'cross db ownership chaining' is 1
    2. Database owners of both databases involved into cross db chaining are the same (same sys.databases.owner_sid, as it is domain windows account - sysadmin)
    3. Database compatibility is the same, 120.
    4. Like I said, all objects owners involved has sys.objects.principal_id = NULL, and all schema owners are dbo (rpt, and dbo schema)

    What else could be wrong???

    Pedja


    Pedja

    Tuesday, July 24, 2018 5:13 PM

Answers

  • Is the SID for dbo the same inside the two databases? The SID for dbo in the database should match sys.databases, but they can be out of sync, if you have restored a database from one server to another.

    Beware that cross-db chainging is a potential security risk. If there are users who are only db_owner on database level (can also suffice with some lower DB permissions) they can elevate their permissions to sysadmin.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, July 24, 2018 10:24 PM
  • How can we sync them?
    Use ALTER AUTHORIZATION to change the owner of the database. You may have to first change do a dummy user, and then back to the correct one.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, July 25, 2018 9:45 PM

All replies

  • Is Public role enabled?
    Tuesday, July 24, 2018 7:12 PM
  • Is the SID for dbo the same inside the two databases? The SID for dbo in the database should match sys.databases, but they can be out of sync, if you have restored a database from one server to another.

    Beware that cross-db chainging is a potential security risk. If there are users who are only db_owner on database level (can also suffice with some lower DB permissions) they can elevate their permissions to sysadmin.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, July 24, 2018 10:24 PM
  • What else could be wrong???


    In addition to the mismatched dbo login/user SID Erland mentioned, dynamic SQL within the proc will break the ownership chain. A secure solution that case is module signing (http://www.sommarskog.se/grantperm.html).


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, July 25, 2018 1:57 AM
  • That might be the reason... For both databases: 

    sys.databases.owner_sid = 0x010500000000000515000000ADCD10C1047E4D9549A0994225790F00

    BUT, in sys.database_principals, sids are different:

    0x010500000000000515000000ADCD10C1047E4D9549A099420D601500 and 0x01

    How can we sync them? Database owner is windows domain account that is sysadmin through domain group. It does not exist as user in any of those dbs. We cannot use dbo as parameter in sp_change_users_login.

    Thanks

    PS we are aware that cross-db chaining is a potential security risk



    Pedja

    Wednesday, July 25, 2018 7:57 PM
  • We do not have dynamic sql in the proc.

    Thanks


    Pedja

    Wednesday, July 25, 2018 7:58 PM
  • How can we sync them?
    Use ALTER AUTHORIZATION to change the owner of the database. You may have to first change do a dummy user, and then back to the correct one.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, July 25, 2018 9:45 PM