locked
sys.tables and cross-database ownership chaining RRS feed

  • Question

  • [ Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb  9 2007 22:47:07   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) ]

    I have a view in database A, owned by dbo, that accesses some tables in another database B on the same server.

    I have cross-database ownership chaining enabled on the server.

    The view works fine when run as sa.

    I have a less-privileged login that is a user in both databases. In A it has select permission on the view. In B it doesn't have select access to any tables.

    If this login selects from the view in A, it can see tables in B that it can't select directly, so the cross-database ownership chaining is working.

    But, the problem is that B.sys.tables appears empty when accessed through the view on A. I know this view hides tables you don't have select access to, but shouldn't it take the cross-database ownership chaining into account and show you all tables in B owned by dbo?

    The reason I want to do this is to get an comparison of estimated table sizes between the two databases using sys.dm_db_partition_stats for the estimate and sys.tables for the table name.

    Even if I try to use OBJECT_NAME() in a view on B and call that from the view on A it still doesn't let me see the table names.

    So I can see the contents of the tables (because of cross-database ownership chaining) but I can't get a list of their names. Is this behaviour by design and are there any workarounds?
    Thursday, November 12, 2009 2:01 PM

Answers

  • The behavior you are seeing is by design.  Ownership chaining is essentially bypassing the select acces check on table B because the view in A has the same owner as the table in B and cross database ownership chaining is enabled.  When you query sys.tables or object_name() the access check is made and since you do not have permissions on the table in B no data is returned.  You could work around this problem by granting the VIEW DEFINITION permission on the table in B to the caller of the view.  This will expose the table's metadata to the gratee of the permission.

    Be careful with VIEW DEFINITION, if you grant it on a proc or function the grantee can see the t-sql definition of the proc or the module.

    Also, be cautious enabling cross database ownership chaining.  If your databases share a common owner, like sa, you can give people witht he ability to create procs and views access to data that you did not anticipate.

    HTH,

    -Steven Gott
    SQL Server
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, November 12, 2009 7:42 PM

All replies

  • May be the fact that sys.tables is not owned by schema DBO , the owner is sys schema.
    Thanks, Leks
    Thursday, November 12, 2009 4:28 PM
  • The behavior you are seeing is by design.  Ownership chaining is essentially bypassing the select acces check on table B because the view in A has the same owner as the table in B and cross database ownership chaining is enabled.  When you query sys.tables or object_name() the access check is made and since you do not have permissions on the table in B no data is returned.  You could work around this problem by granting the VIEW DEFINITION permission on the table in B to the caller of the view.  This will expose the table's metadata to the gratee of the permission.

    Be careful with VIEW DEFINITION, if you grant it on a proc or function the grantee can see the t-sql definition of the proc or the module.

    Also, be cautious enabling cross database ownership chaining.  If your databases share a common owner, like sa, you can give people witht he ability to create procs and views access to data that you did not anticipate.

    HTH,

    -Steven Gott
    SQL Server
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, November 12, 2009 7:42 PM