Options

How to disable a database in SQL?

chandrurecchandrurec Member Posts: 560
Hi all,

I have 2 databases named (TEST & LIVE)in the same server.I want to disable TEST database so that users login through finsql for accessing this TEST database. How to disable the TEST database so that the users will not be allowed to login this TEST database.

IF anyone knows the solution for disabling a database in SQL, Kindly let me know.

Thanks in advance.

Regards,
chandru.

Comments

  • Options
    mohana_cse06mohana_cse06 Member Posts: 5,504
    Did you try by Unchecking the database in User Mapping setup in SQL management studio?
  • Options
    chandrurecchandrurec Member Posts: 560
    Hi mohana_cse06 ,

    I tried it but still I can access the database.

    Regards,
    chandru.
  • Options
    mohana_cse06mohana_cse06 Member Posts: 5,504
  • Options
    chandrurecchandrurec Member Posts: 560
    Hi mohana_cse06 ,

    I selected the login id for which I want to disble the TEST database, then went to user mapping properties then it lists all the db in right hand side,I unchecked the TEST Db.

    Regards,
    chandru.
  • Options
    ChinmoyChinmoy Member Posts: 359
    Which version of SQL Server r u using? you can try the following:

    1. Open SQL Server Management Studio
    2. Expand the Databases
    3. Right click on the TEST database
    4. Select Tasks --> Take Offline

    Note: remember to shut down all services that may be using the database first.

    Or, alternatively you can change the passwords of the database logins in the TEST database, or remove the LIVE users and create new set of users for the TEST database.

    Hope this helps.

    Chn
  • Options
    sprashanthsprashanth Member Posts: 18
    If you want to DISABLE the database to all users then you can make the database offline, else if you want to disable only to few users then goto Database->right click->Properties->Options->Restrict Access and select from the list.
    Cheers,
    Prashanth.
  • Options
    krikikriki Member, Moderator Posts: 9,098
    In SSMS under Security=>Logins, you have all the logins of the users that can access SQL Server.
    If you open the properties of a login, go to tab "User Mappings". Remove the toggle in column "MAP" of users that may not enter the DB. Of course your users should NOT be system administrators (they should NEVER be!).

    Another way is to disallow access through the NAV security tables by removing their account in it (if you are using SQL Server accounts and not Windows accounts, you can put an "Expiration Date" in the past).
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


Sign In or Register to comment.