Post reply

Select permission failed on underline object in Views

  • April 25, 2013 at 12:27 pm

    #277192

    I need to configure a user to run only a view and nothing else on the server (SQL Server 2008 R2). They should have permission to run the view and see the data it returns but not have permission to the underlying tables. Lets say the view is called TestView and the user is called TestUser. What SQL script can be run to configure this user with permissions to only run the view which exists in a database called TestDatabase?

    I did the following things but still getting error message like "Select permission was denied on the object" and this object is a table which is on different databases.

    1. Enabled the ownership chaining.

    2. Views and tables owners are dbo.

    Any help would be appreciated.

  • April 25, 2013 at 12:29 pm

    #1610084

    If you are crossing databases the user needs access to the table(s) in the other databases.

  • April 25, 2013 at 12:33 pm

    #1610092

    These views are filtered views based on the tables and tables contains all more info. If I give access to the table then user will be able to see all the data.

  • April 25, 2013 at 3:07 pm

    #1610142

    I believe, that if ownership chaining is good and the explicit dbo is the same in both databases,

    then all that is needed is for the login to be a user in the database where table(s) are located,

    in addition to of course having select permission on the view in the database where the view is located.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply