Single user database

Applies to:

SQL Server (all supported versions)

This topic defines exactly how to set a user-identified database to single-user mode in SQL Server by making use of SQL Server Management Studio or Transact-SQL. Single-user mode mentions that just one user at a time have the right to accessibility the database and is mainly used for maintenance actions.

You watching: Single user database

In This Topic

Before You Begin

Limitations and Restrictions

If various other customers 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 allude, a various user, yet just one, deserve to connect to the database.

Prerequisites

Security

Permissions

Requires ALTER permission on the database.

Using SQL Server Management Studio

To set a database to single-user mode

In Object Explorer, connect to an instance of the SQL Server Database Engine, and also then expand also that circumstances.

Right-click the database to readjust, and also then click Properties.

In the Database Properties dialog box, click the Options page.

See more: Download Free Fone Rescue For Macos, Fone Rescue

From the Restrict Access alternative, select Single.

If other customers are associated to the database, an Open Connections message will appear. To readjust the property and also close all various other relations, click Yes.

You have the right to likewise collection the database to Multiple or Restricted accessibility by using this procedure. For even more information around the Restrict Access alternatives, view Database Properties (Options Page).

Using Transact-SQL

To collection a database to single-user mode

Connect to the Database Engine.

From the Standard bar, click New Query.

See more: How Do I Restore The Firefox Icon Missing Windows 10 : Firefox

Copy and paste the adhering to example right into the query home window and click Execute. This instance sets the database to SINGLE_USER mode to acquire exclusive accessibility. The instance then sets the state of the AdventureWorks2012 database to READ_ONLY and retransforms accessibility to the database to all individuals.The termicountry option WITH ROLLBACK IMMEDIATE is mentioned in the first ALTER DATABASE statement. This will certainly reason all incomplete transactions to be rolled back and also any kind of various other relationships to the AdventureWorks2012 database to be instantly disassociated.

USE master;GOALTER DATABASE AdventureWorks2012SET SINGLE_USERWITH ROLLBACK IMMEDIATE;GOALTER DATABASE AdventureWorks2012SET READ_ONLY;GOALTER DATABASE AdventureWorks2012SET MULTI_USER;GO

See Also

ALTER DATABASE (Transact-SQL)