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.
Best Answer
Granting select permission on the view would be sufficient if the view and tables all had the same owner. This is called Ownership Chaining:
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:
Granting select on the view:
...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.