Question:Can a user return a recordset from a VIEW , if the underlying tables have not being given explicit read rights?
I’ve received this error message when attempting to access a VIEW
Msg 229, Level 14, State 5, Line 1 The SELECT permission was denied on the object 'musicians_VIEW', database 'MY_DB', schema 'dbo'.
Answer:A user does not require explicit SELECT permissions on table to view data through a VIEW, as long as the user has SELECT permissions on the VIEW.
Granting the user SELECT permissions on the VIEW will stop this error message appearing.
In a situation of granting permissions to a specific object ( as opposed to the wider db_datareader role) , I prefer to create a ROLE and then assign the ROLE to the user permission set.
If explicit SELECT permissions are not granted on the underlying either through a GRANT or db_datareader, the user cannot access the underlying table directly and will receive a message such as:
Msg 229, Level 14, State 5, Line 1 The SELECT permission was denied on the object 'musicians', database 'MY_DB', schema 'dbo'.
From a management perspective, it is easier to manage a list of ROLES , rather than explicit permissions on individual objects. When the list of objects grows and user requirements become more granular , issuing a security audit can be a complicated task.