How to allow a view to read from multiple schemas

Question

I have two tables in different schemas, one owned by dbo, the other not. I have a third schema where I'm trying to create a view that reads from those two tables. My understanding is that if dbo is the owner of the view, because it has grant options to all objects in the database, users with only access to the view should be able to query it. But that isn't working, even when I explicitly grant dbo SELECT WITH GRANT to the underlying table in the schema it doesn't own.

I've read a few other posts, but don't see an answer that works: https://stackoverflow.com/a/4134892/1499015, Ownership Chaining and Tutorial: Ownership Chains and Context Switching

I assume I'm doing something wrong with permission chaining, but I can't figure out what the problem is.

--use master
--GO
--create login testuser with password='******************'
-- principal3 will own schema3
--create login principal3 with password='*****************'

--use testingDB
--GO
create user testuser for login testuser
create user principal3 for login principal3
GO
-- testrole that will only have select permissions on schema2
CREATE ROLE testrole
exec sp_addrolemember 'testrole', 'testuser'
GO

-- the first schema is owned by dbo
CREATE SCHEMA schema1 authorization dbo
GO
-- the schema where the view will live, also owned by dbo
create schema schema2 authorization dbo
GO
-- schema3 owned by principal3
create schema schema3 authorization principal3
go
-- tables in schema1 and schema3
CREATE TABLE schema1.testtable1 (
    id int
    , testvalue sysname
)
GO
CREATE TABLE schema3.testtable3 (
    id int
    , testvalue sysname
)
GO
-- some base data
INSERT schema1.testtable1
SELECT 1, 'a'
UNION SELECT 2, 'b'
UNION SELECT 3, 'c'

INSERT schema3.testtable3
SELECT 1, 'hot dogs rule'
UNION SELECT 2, 'pizza sucks'
UNION SELECT 3, 'haw just kidding that''s obviously backwards'
GO

-- view in schema2 that queries tables in schema1 and schema3, owned by dbo
CREATE VIEW schema2.testview
AS 
SELECT t1.testvalue as v1
    , t3.testvalue as v3
FROM schema1.testtable1 t1
    INNER JOIN schema3.testtable3 t3 ON t1.id = t3.id
GO

GRANT SELECT ON schema::schema1 TO dbo WITH GRANT OPTION
GRANT SELECT ON schema::schema3 TO dbo WITH GRANT OPTION

ALTER AUTHORIZATION ON schema2.testview TO dbo

-- give testrole permission to query the view
GRANT SELECT ON schema2.testview to testrole
GO

EXECUTE AS USER = 'testuser';
SELECT USER_NAME();  
BEGIN TRY  
    select top 100 * from schema2.testview
END TRY  
BEGIN CATCH  
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
       ,ERROR_MESSAGE() AS ErrorMessage;  
END CATCH  
REVERT;  
SELECT  USER_NAME();

When I run this code, I get

The SELECT permission was denied on the object 'testtable3', database 'testingDb', schema 'schema3'.

I've also tried to create a schema owner user for the schema that the view lives in, and granting that user select with grant to the underlying tables, but it's still not working.

Any ideas what I'm doing wrong? Thanks!

"My understanding is that if dbo is the owner of the view, because it has grant options to all objects in the database, users with only access to the view should be able to query it." no, this is wrong. Permission chaining only works when the owner of the objects is the same, and you explicitly state that isn't the case: "one owned by dbo, the other not". If they have separate owners, then a user will need explicit access to the object. — Larnu
Side Note: sp_addrolemember has been deprecated since SQL Server 2008 (if I recall correctly, if not certainly since 2012); you should really be use ALTER ROLE. — Larnu
Some relevant documentation: Ownership Chaining and Tutorial: Ownership Chains and Context Switching — Larnu
I'm not sure if I'm misunderstanding what you're saying - dbo has grant permission to all objects in the database, right? Even though dbo is not the owner of schema3, dbo should be able to grant select permission to other users to any object in schema3, right? And because of that, when the view is owned by dbo, and dbo gives select permission to the view, it should allow the testuser to use the view, right? — gfreeman
No, again, that isn't how ownership chaining works; as your own attempts demonstrate. The documentation discusses this at far more length. dbo doesn't own schema3, so no chaining can occur between schema3 and a schema owned by dbo, nor vice versa. dbo does not have access to everything, it just has access to things it owns; and it is by default the owner of objects unless you specify otherwise. You have specified otherwise, thus it is no longer the owner. — Larnu

Razvan Socol Razvan Socol · Accepted Answer · 2020-11-13T06:06:04

The documentation says:

When an object is accessed through a chain, SQL Server first compares the object's owner to the owner of the calling object (the previous link in the chain). If both objects have the same owner, permissions on the referenced object are not checked. Whenever an object accesses another object that has a different owner, the ownership chain is broken and SQL Server must check the caller's security context.

In this case, when the object testtable3 is accessed, SQL Server first compares testtable3 owner to the owner of the testview. If both objects have the same owner, permissions on the referenced object are not checked. It does not matter if you GRANT dbo rights on testtable3, even WITH GRANT (he this right anyway). It matters that the owner of testtable3 is the same as the owner of the testview.

For example, try:

ALTER AUTHORIZATION ON SCHEMA::schema3 TO dbo

And it will work.

If the owners are different, the ownership chain is broken and SQL Server must check the caller's security context. In this case: does testuser have rights to select from testtable3?.

Therefore, if the owners are different you need to do this to make it work:

GRANT SELECT ON schema3.testtable3 TO testuser

How to allow a view to read from multiple schemas

1 Answers