SQL : Sharing tables among different databases (Cross-Database Ownership Chaining)

Introduction

In a former blogpost of mine, I've described the working of ownership chaining, but this works only within a database and not between databases. In case you have multiple database, a more sophisticated approach is needed. There are several options like using TRUSTHWORTHY or certification of database objects, but I've focused on the option Cross-Database Ownership Chain in this blogpost. The reason I want to use this, is because I've several databases for a datawarehouse and I want to configure the security of some data in a so-called meta database and I don't want to let the end users looking in this 'system' database. That's why I'm investigating Cross-database Ownership Chaining.

Cross-Database Ownership Chains

As seen on SQLServerCentral Cross-Database Ownership chains are an extension of ownership chains where the objects calling and the called object resides in the same database. The only difference is that cross-database ownership crosses database boundaries. In my scenario, I evaluate the current user with USER_NAME() in a view against a table in an other database. My situation would be something like this.  

Let's see what happens when we try to query a view in a database that references a table in another database. We use the same script as in my former post about ownership chains and adjust this to a script where we have two databases. Now, let's test cross-database Ownership chaining. Let's start simple and see whether the following situation works.

Preparation

Here are the preparation scripts that I've used for testing the different scenarios. So again I've a View vwBla in CrossDatabase A that queries a table in CrossDatabaseB.

-----------------------------------------------------------------------
-- 1. Create the necessary logins.
USE [master]
GO

DROP LOGIN [WIN-QTLB6QR5HC9\TestUserA]
GO

CREATE LOGIN [WIN-QTLB6QR5HC9\TestUserA] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

-----------------------------------------------------------------------
-- 2. Create database
USE master
GO 

DROP DATABASE [CrossDatabaseA]
GO 

CREATE DATABASE [CrossDatabaseA]
GO

DROP DATABASE [CrossDatabaseB]
GO 

CREATE DATABASE [CrossDatabaseB]
GO
-----------------------------------------------------------------------
-- 3. Create the table.

USE [CrossDatabaseB]
GO

/*
DROP TABLE [dbo].[tblBla]
GO
*/

CREATE TABLE [dbo].[tblBla](
 [bla] [nchar](10) NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[tblBla]
           ([bla])
     VALUES
           ('blaaaaaaa')
GO
-----------------------------------------------------------------------
-- 4. Create the view.
USE [CrossDatabaseA]
GO

/*
DROP VIEW [dbo].[vwBla]
GO
*/

CREATE VIEW  [dbo].[vwBla] AS 
SELECT * FROM CrossDatabaseB.dbo.tblBla
GO

-----------------------------------------------------------------------
-- 5. Setup the users.
USE [CrossDatabaseA]
GO

CREATE USER [WIN-QTLB6QR5HC9\TestUserA] FROM LOGIN [WIN-QTLB6QR5HC9\TestUserA]
GO

CREATE USER [WIN-QTLB6QR5HC9\TestUserB] FROM LOGIN [WIN-QTLB6QR5HC9\TestUserB]
GO

GRANT SELECT ON [vwBla] TO [WIN-QTLB6QR5HC9\TestUserA]
GO


USE [CrossDatabaseB]
GO

CREATE USER [WIN-QTLB6QR5HC9\TestUserA] FROM LOGIN [WIN-QTLB6QR5HC9\TestUserA]
GO
 

Now I'm doubting about that I've to give access to the CrossDatabaseB in order to make this work and I don't really want this (in my real world situation). I don't want to even want the enduser know there is a CrossDatabaseB. But I haven't seen and found a solution with Cross Database Ownership chain that this supports. Please leave a comment below if you know how.

1. Test the initial setup for TestUserA

In this scenario I've tested whether the TestUserA has access to the View and the underlying table (that resides in the other database).

-----------------------------------------------------------------------
-- 1. Test the setup for TestUserA
USE [CrossDatabaseA]
GO

EXECUTE AS USER = 'WIN-QTLB6QR5HC9\TestUserA';
GO

SELECT * FROM dbo.[vwBla]
GO

SELECT * FROM CrossDatabaseB.dbo.[tblBla]
GO

SELECT User_name()

REVERT;
GO

And this results in :

Msg 916, Level 14, State 1, Line 2
The server principal "WIN-QTLB6QR5HC9\TestUserA" is not able to access the database "CrossDatabaseB" under the current security context.
Msg 916, Level 14, State 1, Line 2
The server principal "WIN-QTLB6QR5HC9\TestUserA" is not able to access the database "CrossDatabaseB" under the current security context.

Well, this result is different from the test I've done in the former blogpost. So, the ownership chaining does not work anymore, although the same user(?) is present in both databases. In order to make sure that it really does not work I tried to get the data with PowerPivot. And it does also not work!

And in PowerPivot an error happens:

What could be the problem that causes this error? Well, I haven't found a real reason than that you should turn on cross-database ownership on. Okay, well let's do that. Off course, the best practice is to turn it on, on database level and not on server level because on server level every database is ownership crossed.

ALTER DATABASE [CrossDatabaseA] SET DB_CHAINING ON;
ALTER DATABASE [CrossDatabaseB] SET DB_CHAINING ON;

Let's test some scenario's further...

2. Test now with cross-database ownership on

Okay, the same test as with did earlier in this blogpost, but now with cross-database ownership on.

USE [CrossDatabaseA]
GO

EXECUTE AS USER = 'WIN-QTLB6QR5HC9\TestUserA';
GO

SELECT * FROM dbo.[vwBla]
GO

SELECT * FROM CrossDatabaseB.dbo.[tblBla]
GO

SELECT User_name()

REVERT;
GO

And this surprises me:

Msg 916, Level 14, State 1, Line 2
The server principal "WIN-QTLB6QR5HC9\TestUserA" is not able to access the database "CrossDatabaseB" under the current security context.
Msg 916, Level 14, State 1, Line 2
The server principal "WIN-QTLB6QR5HC9\TestUserA" is not able to access the database "CrossDatabaseB" under the current security context.

But when I run Excel/Powerpivot as a different user it works(?!):

So, there something going on with the statement "EXECUTE AS USER" in SQL Server. It differs from the "Execute as different user" in Windows. I should investigate this further but I don't have time to do this. If someone has answer to this, please add an comment under this blog. Okay, let's try another thing and run the script for TestUserB in SQL server and to make sure, run PowerPivot as another user.

3. Test the lab situation with another user

In this scenario I'll test the cross database ownership by using another TestUser, TestUserB and sees what happens with the security. What does TestUserB see?

-----------------------------------------------------------------------
-- 2. Test the setup for TestUserB
USE [CrossDatabaseA]
GO

EXECUTE AS USER = 'WIN-QTLB6QR5HC9\TestUserB';
GO

SELECT * FROM dbo.[vwBla]
GO

SELECT * FROM CrossDatabaseB.dbo.[tblBla]
GO

SELECT User_name()

REVERT;
GO

And this result in the following error messages:

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'vwBla', database 'CrossDatabaseA', schema 'dbo'.
Msg 916, Level 14, State 1, Line 2
The server principal "WIN-QTLB6QR5HC9\TestUserB" is not able to access the database "CrossDatabaseB" under the current security context.

And with "Run as an different user" option:

Okay, this seems to be working.

4. Test the setup with the guest user

I've read on  mssqltips.com you can give access to users to a database with the guest user with Cross Database Ownership. Now when I try this I don't seem to get it working as I would believe it should be working. This is was I tried (got this from blog of nabeelshahid).

USE [CrossDatabaseB]
GO
EXEC sp_grantdbaccess 'GUEST'
GO
GRANT CONNECT to GUEST
GO

GRANT SELECT ON [tblBla] to GUEST
GO

And running the script again for user TestUserA gives the follwoing results :

The Guest user is granted CONNECT and SELECT rights on the CrossDatabaseB where the table resides and when I query the view and the table the TestUser gets all the data from the view and from the table in the other database.  On Microsoft site I find this information about the guest user:

"In SQL Server, a special user, guest, exists to permit access to a database for logins that are not mapped to a specific database user. Because any login can use the database through the guest user, it is suggested that the guest user not be enabled."

So, it seems not a best practice to use the guest user for granting right to a database if you want shield the table from viewing the data from unwanted users. I don't understand why this is suggested by the different sites.

Conclusion

In this blogpost I've described cross-database ownership and although I recieved some unexpected results I've a basis to work from and to extend in my real world situation.

Greetz,

Hennie