Set a Database to Single-user Mode

Limitations and Restrictions
• If other users are connected to the database at the time that you set the database to single-user mode, their connections to the database will be closed without warning.
• The database remains in single-user mode even if the user that set the option logs off. At that point, a different user, but only one, can connect to the database.

Prerequisites
• Before you set the database to SINGLE_USER, verify that the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When this option is set to ON, the background thread that is used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode.

sp_helpdb YourDatabaseName
GO
-- Must have AUTO_UPDATE_STATISTICS enabled first
ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS_ASYNC ON/OFF
GO
sp_helpdb YourDatabaseName
GO


Using SQL Server Management Studio
1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Right-click the database to change, and then click Properties.
3. In the Database Properties dialog box, click the Options page.
4. From the Restrict Access option, select Single.
5. If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.

Using Transact-SQL
1. Connect to the Database Engine.
2. From the Standard bar, click New Query.
3. Copy and paste the following example into the query window and click Execute.

This example sets the database to SINGLE_USER mode to obtain exclusive access. The example then sets the state of theAdventureWorks2014 database to READ_ONLY and returns access to the database to all users. The termination option WITH ROLLBACK IMMEDIATE is specified in the first ALTER DATABASE statement. This will cause all incomplete transactions to be rolled back and any other connections to theAdventureWorks2014 database to be immediately disconnected.

USE master;
GO
ALTER DATABASE AdventureWorks2014
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2014
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks2014
SET MULTI_USER;
GO