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:10 AM


  • 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.

    • Marked as answer by goms Tuesday, July 28, 2009 9:00 AM
    Tuesday, July 28, 2009 8:48 AM