Sql-server – Does cross-database permission chaining work for objects owned by sa/dbo

permissionssql serversql-server-2012view

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?

Best Answer

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
Related Question