Results 1 to 10 of 10

Thread: User Access in SQL 2005

  1. #1
    Join Date
    Apr 2008
    Posts
    5

    User Access in SQL 2005

    Hi,

    I want to grant a user to access only one view in my database and disable the rest.

    Is there a way to do this in SQL 2005.

    Thanks in advance

    Pegeot2000

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Sure, when you create the user in a database do not grant any default role. Then run

    grant select on yourview to theuser

  3. #3
    Join Date
    Apr 2008
    Posts
    5
    When a user is created, the public role is assigned automatically and the public user has select permission which gives the user full access to view any table or view.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    No, public role doesn't have select permission by default.

  5. #5
    Join Date
    Apr 2008
    Posts
    5
    But the problem is when I just create a user, it can view all the tables and views in the primary database.

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Can see table names or can query from tables?

  7. #7
    Join Date
    Apr 2008
    Posts
    5
    Yes, both options are possible with a new user, who’s not given any permission except for the public role which is assigned automatically at the time of creating it.

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    You have to double check public role's permission in the db. It doesn't have permission on user tables by default.

  9. #9
    Join Date
    Apr 2008
    Posts
    5
    Could you kindly tell me how to check on the Public role’s permission in SQL 2005 and how I could change them according to my needs.

  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    Open public role's properties in ssms and look at securables.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •