I have created a new SQL user who is only allowed to select from a single view in schema [PBI]. We don't want them to see any other tables or views, just the one view.
Reading online, it looks like it should be a simple Grant, such as:
GRANT SELECT ON [PBI].[View_OrderTake] TO [ReportingUser]
However when I login as that user and attempt to select from the view, I get the error below:
The SELECT permission was denied on the object 'Orders', database 'OrdersDB', schema 'dbo'.
If I recreate the view to also be under the DBO schema and then grant access to that, the select works.
Therefore how can I grant select only access to the view under the PBI schema, but for it to allow tables in the underlying DBO to be read? The user is not allowed to see any other tables or views, aside from the single view in PBI.
@Ifor - The Ownership chaining articles were really useful. After quite a bit of digging I found that the issue was the schemas, DBO and PBI had different owners. Therefore even though I was granting the user SELECT access to the PBI view, as it referenced DBO tables which had a different owner the select was denied.
By changing the owner of both schemas to be the same, the select now works and the user can only see this one view and not all the underlying tables when connecting.