SQLTeam.com | Weblogs | Forums

Grant SELECT to View but where tables are in different schema

Hi there,

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.

Many thanks!

Mark

DENY will override GRANT. The user was DENYd SELECT on the Orders table.

Hi @ScottPletcher

Thanks for taking a look..

So are you saying that a DENY exists for everything that hasn't been explicitly granted a SELECT, ie the DBO tables used in the PBI view?

Yet if the view was under DBO, the SELECT will allow the underlying DBO tables to be read?

Could you help suggest a way to fix my issue?

Thanks

No, I'm saying an explicit DENY was specified for that table, either directly or indirectly (such as by a DENY on the schema, for example).

Hi both, thanks again for your help.

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

Thanks

Mark