none
Privilege RRS feed

  • Question

  • In sqlserver 30 database is up and running ....
    But all the users are having sysadmin privilege...
    How to revoke the privilege from users..
    or users will have the rights to access only sample database....
    they wont be allowed to drop other transaction tables in some other database ...
    How to restrict the  users to drop,truncate database ?
    They have to be allowed to drop ,truncate only sample db not for production database

    How to do that?

    Tuesday, July 28, 2009 8:09 AM

Answers



  • Perform following steps.


    1-Click Security Folder
    2-Open Login Folder
    3-Double Click on Login that you need to assign rights
    4- Double Click on on user (you need to assign permission)
    5-Click on Server Roles and uncheck the sysadmin checkbox
    6-Click User Mapping
    7-Select the Sample Database and check

    8-On Database Role membership for: Database check the box db_owner  
    9-Click OK

    Now Login with user and you have permission only sample database.


    Aamir

    • Marked as answer by goms Tuesday, July 28, 2009 11:10 AM
    Tuesday, July 28, 2009 8:30 AM
  • Well i think you have to grant permissions to the users go to security then click login at the main there you can see the logins for the different users
    and which databases they are mapped with click the user uncheck sysadmin role assign it public then go to the usermapping tab select the database under that you can see the roles for a user uncheck all if any role is checked uncheck it and then assign the db_datareader now go back to the the databases select the temp database which you want to use as temporary or practice database and grant db_owner role to the user 
    • Marked as answer by goms Tuesday, July 28, 2009 11:04 AM
    Tuesday, July 28, 2009 9:07 AM

All replies



  • Perform following steps.


    1-Click Security Folder
    2-Open Login Folder
    3-Double Click on Login that you need to assign rights
    4- Double Click on on user (you need to assign permission)
    5-Click on Server Roles and uncheck the sysadmin checkbox
    6-Click User Mapping
    7-Select the Sample Database and check

    8-On Database Role membership for: Database check the box db_owner  
    9-Click OK

    Now Login with user and you have permission only sample database.


    Aamir

    • Marked as answer by goms Tuesday, July 28, 2009 11:10 AM
    Tuesday, July 28, 2009 8:30 AM
  • Well i think you have to grant permissions to the users go to security then click login at the main there you can see the logins for the different users
    and which databases they are mapped with click the user uncheck sysadmin role assign it public then go to the usermapping tab select the database under that you can see the roles for a user uncheck all if any role is checked uncheck it and then assign the db_datareader now go back to the the databases select the temp database which you want to use as temporary or practice database and grant db_owner role to the user 
    • Marked as answer by goms Tuesday, July 28, 2009 11:04 AM
    Tuesday, July 28, 2009 9:07 AM