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
Same procedure would be repeated to set the database back to multiple user mode