Does cross-database permission chaining work for objects owned by sa/dbo?

Question

I have two databases, both owned by sa. All objects in both databases are in schema dbo.

I have a user bob in dbSafe who needs to select from a view that joins tables from both dbSafe and dbRestricted. I don't want to grant bob direct permission to those tables in dbRestricted, since they contain columns I don't want bob to see.

I've granted bob SELECT permission on the view, and SET DB_CHAINING ON for both databases, but I'm still getting an error:

The server principal "bob" is not able to access the database "dbRestricted" under the current security context.

Am I misunderstanding what cross-database chaining does?

If the source object in the source database and the target objects in the target databases are owned by the same login account, SQL Server does not check permissions on the target objects.

Can database chaining work in this scenario, with database owner sa and schema dbo? Or does it have to be an explicit login account/non-default schema?

The accepted answer to this question suggests creating a view in dbRestricted and assigning direct permission to bob, would that work in this case? What if adding a view to that database isn't an option for me (due to developer/vendor restrictions)? Isn't that what cross-database chaining is for?

Answer 1

You need to create a user for bob in dbRestricted. You do not need to grant bob access to the table in dbRestricted.

This minimally complete verifiable example shows this in action:

USE master;
CREATE DATABASE db_a;
ALTER DATABASE db_a SET DB_CHAINING ON;
CREATE DATABASE db_b;
ALTER DATABASE db_b SET DB_CHAINING ON;
CREATE LOGIN db_a_login WITH PASSWORD = 'aasdfasdfasdf78723%';
GO

USE db_a;
CREATE TABLE dbo.t
(
    someval varchar(10) NOT NULL
);
INSERT INTO dbo.t (someval) VALUES ('db_a');

CREATE USER db_a_login FOR LOGIN db_a_login;

USE db_b;
CREATE TABLE dbo.t
(
    someval varchar(10) NOT NULL
);
INSERT INTO dbo.t (someval) VALUES ('db_b');
CREATE USER db_a_login FOR LOGIN db_a_login;


USE db_a;
GO
CREATE VIEW dbo.both_t
AS
SELECT *
FROM db_a.dbo.t
UNION ALL
SELECT *
FROM db_b.dbo.t;
GO
GRANT SELECT ON dbo.both_t TO db_a_login;
GRANT SELECT ON dbo.t TO db_a_login;
GO

EXECUTE AS LOGIN = 'db_a_login';
SELECT *
FROM dbo.both_t;
REVERT

╔═════════╗
║ someval ║
╠═════════╣
║ db_a    ║
║ db_b    ║
╚═════════╝

If we remove cross-database-ownership-chaining, we see this fails:

ALTER DATABASE db_a SET DB_CHAINING OFF;
ALTER DATABASE db_b SET DB_CHAINING OFF;

EXECUTE AS LOGIN = 'db_a_login';
SELECT *
FROM dbo.both_t;
REVERT

Msg 229, Level 14, State 5, Line 50
The SELECT permission was denied on the object 't', database 'db_b', schema 'dbo'.

Use this to clean up afterwards:

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'db_a') 
DROP DATABASE db_a;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'db_b') 
DROP DATABASE db_b;
IF EXISTS (SELECT 1 FROM sys.server_principals sp WHERE sp.name = 'db_a_login') 
DROP LOGIN db_a_login;
GO

Answer 2

If, instead of a View, your user bob could either select from a multi-statement Table-Valued Function (TVF) or execute a Stored Procedure, then this can be done more securely (i.e. without enabling Cross-Database Ownership Chaining on either Database, and without creating a User for bob in dbRestricted).

The main problem with Cross-DB Ownership Chaining is that you can only restrict it by not creating an equivalent User in dbRestricted (i.e. emulating the situation which caused you to post this question). But any User in both DBs will now be able to have objects in this DB access objects in dbRestricted. Assuming that most (if not all) objects are in the dbo Schema, or a Schema owned by dbo, then that is pretty much everything. And, even if this View is the only object to access dbRestricted today, you can't prevent others from adding objects to this DB in the future that access objects in dbRestricted.

Instead of creating two potential security holes in your system, you can use Module Signing to accomplish this goal as securely as possible. I have example code showing how in the following answer, also here on DBA.SE:

Access view based on table in another database without account in that other database