Post reply

Please help me on Sql server 2005 User creation Problem.

  • May 25, 2011 at 3:40 am

    #251504

    Please help me on Sql server 2005 User creation Problem.

    I have 1 database in sqlserver 2005. i want to give permission to the user to access all database View of this database.

    I have created a database role and defined all database view on this database role. and have created one user and provide a permission to this database role.

    but when this user use their login, then it can access all database tables and database view of this database

    Can any one help me? And let me know, how can i create such type of user that they can only access database view of any particular database

    your help much be appricate.

    Thanks & regards

    Priyank

  • May 25, 2011 at 3:52 am

    #1329557

    Priyank

    Check what permissions have been given to the public role.

    John

  • May 25, 2011 at 4:39 am

    #1329582

    Hi John,

    Thanks fo replying,

    in Public database role, they have access all content of database, Like SP, tables and view,

    Could be change this Public database role setting for one database, if change then i am not aware about what will be the impact of other users. Can other user access the table and SP afterward if we change?

    Thanks again.

  • May 25, 2011 at 6:39 am

    #1329622

    Users that don't have specific permissions on any particular object will not be able to access that object if you revoke the permissions for the public role on the object.

    John

  • May 25, 2011 at 6:52 am

    #1329627

    Hi John,

    Thanks for you reply,

    Last question, is there any way that we can not select Public database role with the specific user. I have already created another database role which is permitted only to access Database view.

    Overall, if i create any user then only provide this new database role access.

    Is it possible? Please suggest.

    Thanks again.

  • May 25, 2011 at 7:04 am

    #1329636

    You've got two choices. First, you can issue DENY statements on the objects that you don't want members of the first role to access. Second, and the one I would prefer, you can revoke all permissions from public, and create another new role to which you assign the permissions that were previously assigned to public. Make all members that are not members of the first role a member of the new role.

    John

Viewing 6 posts - 1 through 5 (of 5 total)

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