How to disable a database in SQL?

chandrurec

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.

mohana_cse06

Did you try by Unchecking the database in User Mapping setup in SQL management studio?

chandrurec

Hi mohana_cse06 ,

I tried it but still I can access the database.

Regards,
chandru.

mohana_cse06

Can you explain me what you did?

chandrurec

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.

Chinmoy

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

sprashanth

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.

kriki

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