SQL Permissions / Securables - Can I give permissions to a "Select" from a View that uses another View that doesn't have permission granted?

Question

Here's my scenario...

SQL Role

• Staff_User

Scheme

• People

Tables

• People.Persons

• People.PhoneNumbers

Views

• People.vtPersons - The vtPersons view filters the data from the Persons table showing only that which belongs to the currently logged in user.

• People.vtPhoneNumbers - The vtPhoneNumbers view filters the data from the PhoneNumbers table showing only that which belongs to the currently logged in user.

• People.vwContactInformation - The vwContactInformation "View" combines the data from vtPersons and vtPhoneNumbers so it can be used as a query in a Crystal Report.

The Staff_User role has been granted "SELECT" permission to the vwContactInformation view and nothing else.

I am getting an error now saying that permission is denied to the object vtPhoneNumbers. Must I also grant "SELECT" permission to this view? From experience in another SCHEME I did NOT have to do this and everything worked well. But now I'm getting this error in a second SCHEME that I've created. Can anybody suggest what I have in the first scheme that allows the permissions to cascade to views, tables, functions etc that are called from the view made accessible to the role.

Thanks, Justin

Answer 1

Assuming SQL Server (all versions)

The error says "denied": if permissions were missing or incorrect, you'd see something like "does not exist or no permissions". Based on that, I'd check the rights for vtPhoneNumbers and see if any explicit DENY has been set. DENY is always evaluated and takes precedence. (Sorry, can't find it in BOL).

Why:

The idea of ownership chains/chaining means that if all objects are in the same schema (aka owner), permissions on the referenced object are not checked.

In this case, permissions on vtPhoneNumbers and vtPersons should not be checked because all views and tables are in the "People" schema.

Note, REVOKE removes permissions (previously set using GRANT or DENY). Someone may have used DENY not REVOKE to remove a previous GRANT statement on vtPhoneNumbers