Select Permissions Across Multiple Schemas Within the Same Database

Question

I have been asked by a client to implement some views for reporting purposes, the views will be accessed via PowerBI, Excel and SSRS. The designated user will only have access to the view and no underlying tables must be available to the user.

The problem I have encountered in that the SQL within the view touches 3 different schemas (All within the same database):

• Pupil
• Provider
• Security

The view is:

CREATE VIEW dbo.vTestPermissions
AS
SELECT a.Column1,
       b.Column1,
       c.Column1
FROM Pupil.Table1 a
JOIN Provider.Table2 b ON a.Column1 = b.Column1
JOIN Security.Table3 c ON a.Column1 = c.Column1

The owners of the Tables/View are as follows:

• Pupil.Table1 - Owner Pupil
• Provider.Table2 - Owner Provider
• Security.Table3 - Owner Security
• vTestPermissions - Owner dbo

When I select from the view I get an error:

The SELECT permission was denied on the object 'table3', database 'TEST', schema 'Security'

I have tried giving SELECT permission with and without the GRANT option to the schema, and the tables, but that makes the underlying tables available to the user.

Any help on this will be greatly appreciated.

Answer 1

Granting select permission on the view would be sufficient if the view and tables all had the same owner. This is called Ownership Chaining:

When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.

Where the objects have different owners, ownership chaining does not work.

So, the simplest answer is to change the ownership of the tables to match the view:

ALTER AUTHORIZATION ON Pupil.Table1 TO dbo;
ALTER AUTHORIZATION ON Provider.Table2 TO dbo;
ALTER AUTHORIZATION ON Security.Table3 TO dbo;

Granting select on the view:

GRANT SELECT ON dbo.vTestPermissions TO USER = 'your_user';

...will then allow access through the view, via ownership chaining, while preventing direct table access.

If that is not suitable for your needs, we will need to look at a more complex arrangement.