none
Granting SELECT access to a database view RRS feed

  • Question

  • Hello:

    I have a database view which gathers information from two tables. I granted a user "select" access to the database view. However, when the user tries to run the view via a .NET application, he gets "the underlying provider failed on Open". If I grant the user "db_datareader" permissions, he is able to run the view. I really want to restrict the access and not give read access to entire database.

    Do I need to grant additional permissions to the database for this user?

    Any pointers?

    Thanks!

    Monday, February 17, 2020 10:45 PM

Answers

  • Is the view and underlying table owned by different principles. If they are owned by same principles, no other permissions are needed. But it looks like they are owned by different principles, in your case. Refer this article to get idea on what the issue could be : https://sqlity.net/en/2239/ownership-chain/

    Hope it Helps!!


    • Edited by Stan210 Tuesday, February 18, 2020 4:21 AM
    • Marked as answer by diffident Tuesday, February 18, 2020 11:21 PM
    Tuesday, February 18, 2020 4:20 AM

All replies

  • Is the view and underlying table owned by different principles. If they are owned by same principles, no other permissions are needed. But it looks like they are owned by different principles, in your case. Refer this article to get idea on what the issue could be : https://sqlity.net/en/2239/ownership-chain/

    Hope it Helps!!


    • Edited by Stan210 Tuesday, February 18, 2020 4:21 AM
    • Marked as answer by diffident Tuesday, February 18, 2020 11:21 PM
    Tuesday, February 18, 2020 4:20 AM
  • Hi diffident,

    Members of the db_datareader database role can run a SELECT statement against any table or view in the database.

    You could grant select permission to user on view.

    For error “the underlying provider failed on Open”, please refer to this blog which might help.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 18, 2020 8:34 AM