How to Alter a SQL Server Database as Single User Mode and as Multi User Mode

SQL Server database administrators can use SQL Server Enterprise Manager for SQL Server 2000 to set a database to a single user mode or to multi user mode. Similarly, SQL Server Management Studio can be used for SQL Server 2005 for changing a database to single user mode or to multi user mode. Also you can alter the database access mode by using sql commands like : ALTER DATABASE and sp_dboption.

ALTER DATABASE [Works] SET MULTI_USER WITH NO_WAIT

ALTER DATABASE [Works] SET SINGLE_USER WITH NO_WAIT

EXEC sp_dboption 'Works', 'single user', 'false'

EXEC sp_dboption 'Works', 'single user', 'true'

To alter the database state to restrict the access to the single user mode, first open the Microsoft SQL Server Management Studio, and connect to a SQL Server instance. Open the list of available databases belonging to the related instance in the Object Explorer window. Right click on the SQL Server database that you want to set its mode to single user and select Properties in the context menu.

When you click properties menu item, the following Database Properties screen will be displayed for the selected database. I used database named Works for samples used for the article.

SQL Server restrict access

Select the Options page from the list in the left side of the screen. If you scroll down the options list for State options, you will see Restrict Access database options with three options listed in the combo box.

Restrict Access modes : Multiple, Single and Restricted modes.

If you select Single mode and click the OK button, you can either alter the database access mode to single user successfully or you will fail to change the access mode because of existence of active open connections to the Works database. The Management Studio IDE will prompt you to close all other connections to the related database for a successful alter database option process.

SQL Server open connections

To change the database properties, SQL Server must close all other connections to the database. Are you sure you want to change the properties and close all other connections?

After the alter command runs successfully, the database Works will be displayed as shown in the Object Explorer window.

If an active connection exists other than the Management Studio, and you click the database Works within the SQL Server Management Studio, the following warning message will be displayed:

database is not accessible warning message

The database Works is not accessible. (ObjectExplorer)

If you right click on the database Works, the following error message will be displayed.

database is already open

Database 'Works' is already open and can only have one user at a time. (Microsoft SQL Server, Error: 924)

After a database is altered as single user mode, it can be altered again back to multi user mode by running the below sql command.

ALTER DATABASE [Works] SET MULTI_USER WITH NO_WAIT

But if this command is run from a connection that is not the only active connection to the related database, the command will fail with the following message:

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'Works' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

In a case that you want to alter the database access mode from single user mode to multi user mode or from multi user mode to single user mode, you may have to kill all the active open connections to the database.

In such a case, you have to find all the open connections and run "Kill @spid" command to close all the connections.

For a list of open connections for a specific database you can run a similar command as below :

select spid from master..sysprocesses where dbid = db_id('Works') and spid <> @@spid

The second part of the task will be closing the open connections with the Kill command. This can be achieved by running kill commands within a cursor or creating a dynamic sql command which kills the active connections to the related database.

Let's code the closing open connections using a T-SQL cursor:


DECLARE @DatabaseName nvarchar(50)

DECLARE @SPId int



SET @DatabaseName = N'Works'



DECLARE my_cursor CURSOR FAST_FORWARD FOR

SELECT SPId FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId



OPEN my_cursor 



FETCH NEXT FROM my_cursor INTO @SPId



WHILE @@FETCH_STATUS = 0

BEGIN

KILL @SPId



FETCH NEXT FROM my_cursor INTO @SPId

END



CLOSE my_cursor 

DEALLOCATE my_cursor

After all connections are closed except the currently running process (@@spid) if we are trying to alter the access mode of the database that we're in from multi user mode to single user mode, we can now run the ALTER DATABASE command for single user mode or multi user mode.

ALTER DATABASE [Works] SET MULTI_USER WITH NO_WAIT

ALTER DATABASE [Works] SET SINGLE_USER WITH NO_WAIT

You can also use the following sp_dboption sql commands to alter the database option for single user. If you set the "single user" database option to "true", this is same as altering the database as "SINGLE_USER" with ALTER DATABASE statement.

USE master
GO
EXEC sp_dboption 'Works', 'single user', 'false';

If you run the below sp_dboption command which sets the "single user" to "false", this is same as altering the database as "MULTI_USER" with ALTER DATABASE statement.

USE master
GO
EXEC sp_dboption 'Works', 'single user', 'true';

The main difference between sp_dboption and ALTER DATABASE commands is that, sp_dboption will not be supported with further releases of SQL SERVER after SQL SERVER 2005.