INF: Object Ownership Chain Checking Across Databases Depends on the Login That Is Mapped to the Object Owners

Article translations Article translations
Article ID: 272424 - View products that this article applies to.
This article was previously published under Q272424
Expand all | Collapse all

SUMMARY

SQL Server Books Online states that ownership chains are checked for stored procedures and views that cross databases. You can see this easily when the owner of the dependent objects in each database maps to the same login. However, if you have users in both databases that have the same user name, but are not mapped to the same login (as often happens with the database owner [DBO]), it may seem that the ownership chains are being incorrectly checked. For example, if the DBO of each database is mapped to the same login, and the owner of the objects in the databases is the DBO, permissions are not checked. However, if the DBO of each database is mapped to a different login, the ownership chains are checked. This can happen with users other than the DBO if your user names are different from your login names.

MORE INFORMATION

A user in a database can be granted permissions on a view or stored procedure. If that view or stored procedure accesses objects in another database which is owned by a user mapped to a different login, the permissions of the underlying objects are checked. If the user does not have permissions to the objects in the other database, the following error is returned:
Error 229: %ls permission denied on object '%.*ls', database '%.*ls', owner '%.*ls'.
However, if the underlying mapped login of objects in the query is the same, permissions are not checked, and the query executes without the above error even if the user does not have permissions to the underlying tables.

To see an example of this, follow these steps:
  1. Set up a reproduction environment with two different databases; db1 is the "Base" database with the data and db2 contains a view that selects from the table in db1.
    USE master
    GO
    CREATE DATABASE db1
    CREATE DATABASE db2
    EXEC sp_addlogin Owner1
    EXEC sp_addlogin Owner2
    EXEC sp_addlogin TestUser
    GO
    -- setup database #1, DBO is Owner1
    USE db1
    EXEC sp_changedbowner Owner1
    EXEC sp_adduser TestUser
    CREATE TABLE BaseTable (TableColumn CHAR(20))
    INSERT BaseTable VALUES ('Select Succeeded')
    GO
    -- setup database #2
    USE db2
    EXEC sp_adduser TestUser
    GO
    CREATE VIEW      CrossDatabaseView           AS SELECT * FROM db1.dbo.BaseTable
    GO
    GRANT SELECT  ON CrossDatabaseView           TO public
    GO
    					
  2. Execute the following code to see the different results based on whether the DBO is the same or different:
    SET NOCOUNT ON
    GO
    PRINT '***** results with both DBOs the same *****'
    PRINT ''
    USE db2
    EXEC sp_changedbowner Owner1
    SETUSER 'TestUser'
    GO
    SELECT * FROM CrossDatabaseView
    GO
    SETUSER
    GO
    
    PRINT ''
    PRINT '***** results with each DBO different *****'
    PRINT ''
    USE db2
    EXEC sp_changedbowner Owner2
    SETUSER 'TestUser'
    GO
    SELECT * FROM CrossDatabaseView
    GO
    SETUSER
    GO
    					
  3. Clean up the databases and logins created for this example:
    USE master
    GO
    DROP DATABASE db1
    DROP DATABASE db2
    EXEC sp_droplogin Owner1
    EXEC sp_droplogin Owner2
    EXEC sp_droplogin TestUser
    GO
    					

Properties

Article ID: 272424 - Last Review: February 5, 2004 - Revision: 4.3
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbinfo kbpending KB272424

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com