SQL Server ownership chain cross schema with different owners for view selecting from multiple schemas

Question

I have 1 database with multiple schemas, some owned by a different user than the default 'dbo' user.

I have a view in one of these 'dbo' schemas, that selects from 5 tables in other 'dbo' schemas, and then 2 tables in a 'UserA' schema.

I want to grant a user group access to the view in the 'dbo' schema and not the underlying tables. When granting permission to the view, I get errors saying cannot select from the tables owned by 'UserA'. Understandable and expected because the view (and thus authorizations granted) are for 'dbo'. So how do I also grant access to the 'UserA' tables without directly assigning them to my user group.

Any recommendations? I tried to find if there is some way to grant access to the view through both 'dbo' and 'UserA', but it seems only 1 owner can grant select permissions? I also tried making views of the 'UserA' table in the 'dbo' schema and then granting permission to those new 'dbo' views, but that didn't work either.

You talk about "one of these 'dbo' schemas", but you can only have one schema named 'dbo'. I'm assuming that you mean one of the schemas owned by the 'dbo' user, but can you please clarify? Listing the schemas and owners would be of help — DeanOC
The short answer ought to be yes... you should be able to grant positive permissions on anything, and certainly one of the reasons for Views is to separate access to the table. Sounds like maybe this... stackoverflow.com/questions/13032818/… — Mike M
@DeanOC I agree my word choice there was confusing. I mean that I created a view a schema that is owned by dbo. The Schema|Owner relationships are: dbo|dbo, co|dbo, pm|dbo, ua|UserA — wh4tshisf4c3
@MikeM My question is similar to what you have linked to, but I do not see a resolution to that question either. The top result recommends changing the view owner, which I am unable to do as well. — wh4tshisf4c3

Dan Guzman Dan Guzman · Accepted Answer · 2018-07-03T01:42:10

No permissions on the underlying tables are needed when all objects involved are owned by the same user. This is known as ownership chaining in SQL Server.

It seems you have different schemas which are owned by different users, breaking the chain. Tables are owned by the schema's owner by default (i.e. inherited) but this can be overridden by changing the owner at the table level for specialized requirements. Below is an example script that illustrates this method.

Using granular object ownership rather than inheriting the schema owner is not something that should be done routinely. It is not intuitive for most and adds administrative burden.

USE tempdb;
GO

CREATE USER UserA WITHOUT LOGIN;
GRANT CREATE TABLE TO UserA;
CREATE USER UserB WITHOUT LOGIN;
GRANT CREATE TABLE TO UserB;
CREATE USER UserC WITHOUT LOGIN;
CREATE ROLE YourUserGroup;
ALTER ROLE YourUserGroup ADD MEMBER UserC;
GO
CREATE SCHEMA UserA AUTHORIZATION UserA;
GO
CREATE SCHEMA UserB AUTHORIZATION UserB;
GO

CREATE TABLE dbo.Table1(ID int NOT NULL CONSTRAINT PK_Table1 PRIMARY KEY);
GO

EXECUTE AS USER = 'UserA';
GO
CREATE TABLE UserA.Table1(ID int NOT NULL CONSTRAINT PK_Table1 PRIMARY KEY);
GO
REVERT;
GO

EXECUTE AS USER = 'UserB';
GO
CREATE TABLE UserB.Table1(ID int NOT NULL CONSTRAINT PK_Table1 PRIMARY KEY);
GO
REVERT;
GO

CREATE VIEW dbo.View1
AS
SELECT
      t1.ID AS dboTable1ID
    , t2.ID AS UserATable1ID
    , t3.ID AS UserBTable1ID
FROM dbo.Table1 AS t1
JOIN UserA.Table1 AS t2 ON t2.ID = t1.ID
JOIN UserB.Table1 AS t3 ON t3.ID = t2.ID;
GO
GRANT SELECT ON dbo.View1 TO YourUserGroup;
GO

EXECUTE AS USER = 'UserC';
GO
--this fails due to broken ownership chain
SELECT * FROM dbo.View1;
GO
REVERT;
GO

--change table owner to common owner
ALTER AUTHORIZATION ON OBJECT::UserA.Table1 TO dbo;
ALTER AUTHORIZATION ON OBJECT::UserB.Table1 TO dbo;
GO

EXECUTE AS USER = 'UserC';
GO
--this now succeeds because all objects involved are owned by dbo
SELECT * FROM dbo.View1;
GO
REVERT;
GO

DROP VIEW dbo.View1;
DROP TABLE dbo.Table1;
DROP TABLE UserA.Table1;
DROP TABLE UserB.Table1;
DROP SCHEMA UserA;
DROP SCHEMA UserB;
DROP USER UserA;
DROP USER UserB;
DROP USER UserC;
GO

SQL Server ownership chain cross schema with different owners for view selecting from multiple schemas

1 Answers