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.