Cross Database Ownership Chaining fails: not able to access the database

Question

I have:

• A single Microsoft SQL Server 2008 R2
• Two databases with some tables: DB1 and DB2 (both with the same owner)
• A view in DB1, DB1.dbo.View1, that SELECT * FROM DB2.dbo.Table1
• A role, ViewRole, in DB1 granted SELECT permit to the view (no other permissions)
• A Windows user, DOMAIN\user, with server role set to public, who belongs to DB1's ViewRole

When the user tries to SELECT from the view, I get the error:

The server principal "DOMAIN\user" is not able to access the database "DB2" under the current security context

I have tried (and hopefully succeeded) to activate Cross Database Ownership Chaining using both:

EXEC sp_configure 'Cross DB Ownership Chaining', '1';RECONFIGURE

and

EXEC sp_dboption DB1, 'db chaining', 'true'
EXEC sp_dboption DB2, 'db chaining', 'true'

To confirm that the setting is made, I run:

SELECT name, owner_sid, is_db_chaining_on FROM sys.databases

and the result is:

name  owner_sid           is_db_chaining_on
DB1   0x0105...DCB510000  1
DB2   0x0105...DCB510000  1

So, why won't the user be able to SELECT from the view? There will be no error if the view only selects from a table within DB1.

Anyone has any suggestion as to what I might have missed or misunderstood?

Answer 1

The windows user has to have access to both databases to be able to use cross database ownership chaining.

You should also disable the option on server level for security and only enable the option in the databases.

You can read more about it here