Msg 229 Level 14 State 5 - SQL VIEW permissions and underlying tables

06 January,2014 by Jack Vamvas

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.

 Read More

List sql database roles

Use SQL Materialized view to eliminate DISTINCT and speed up response time


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Msg 229 Level 14 State 5 - SQL VIEW permissions and underlying tables


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer