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):
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:
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.
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.
External links referenced by this document: