There may be times when you need to perform some maintenance against a database that requires you to keep normal database users from connecting to your database, while the maintenance is being performed. You want to stop users from connecting right away, but you want the database to be in a state that you can perform maintenance. What do you do?
There are three different modes that a database can be in: MULTI_USER, RESTRICTED_USER, or SINGLE_USER. By putting the database in RESTRICTED_USER mode you will keep normal users from being able to connect to your databases. This mode doesn’t restrict any users that are in the db_owner role, or sysadmin role from connecting to the database. You can put the database in RESTRICTED_USER mode by running the following command:
ALTER DATABASE MyDatabase SET RESTRICTED_USER;Once you put the database in RESTRICTED_USER mode, a database user that is not in the db_owner or sysadmin role will not be able to connect to the database. Keep in mind that having your database in RESTRICTED_USER mode doesn’t kick users out of the database if they are already connected. Therefore, if you need to perform an action that requires all users to be out of the database you would need to first terminate all SPIDs that are using the database. You can do that by issuing the KILL command.
Putting the database in RESTRICTED_USER mode will not keep everyone out. People in the db_owner role or sysadmin role can still connect to the database. If you really want only yourself in the database, you will need to place the database in SINGLE_USER mode. To put the database in SINGLE_USER mode, and kick all the users out of the database you can run the following the following TSQL command:
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;