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:
- 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.