:::: MENU ::::

Set SQL Server database to single user mode or multi user mode

  • Aug 06 / 2008
  • 4
dbDigger, Logins and Users, SET Options, SSMS tips and tricks, T-SQL Scripts

Set SQL Server database to single user mode or multi user mode

Often we require to set the database in to single user mode. And after specified operation we switch to multi user mode. Here is a script to perform the switch operation.
For changing AdventureWorks database to single user mode

 
ALTER DATABASE adventureworks  
 SET SINGLE_USER  
 GO  

and for again to multi user mode

 
ALTER DATABASE adventureworks  
 SET MULTI_USER  
 GO  

Also you have to consider that if some users are connected to that database then their connections will be dropped without any warning. And you can not set the system databases master, tempDB, msDB to single user mode. For that purpose you have to start your SQL Server in single user mode.
You can also set a database to single user mode through GUI. For SSMS i will go through for steps

  • Right click on database to be set user mode
  • Go to Options in left panel
  • In right panel you will found ‘Restrict Access’ option
  • Choose single user mode
  • Click OK

Set single user mode through SSMS

Same procedure would be repeated to set the database back to multiple user mode

  • Anonymous

    Please tell me that for what purposes we may require to set the database to single user mode?

  • Atif Shehzad

    You may need single user mode in case of backup restore, index rebuild. It would make sure your exclusive access to subject database. I would appreciate if some one add any additional scenario where single user mode may be required.

  • Anonymous

    For what purpose is Restricted_User mode is?

  • Atif Shehzad

    It is intermediate state of single and multiple users mode. Only members of the db_owner, dbcreator, or sysadmin roles can use the database in restricted_User mode.

Consult us to explore the Databases. Contact us