Skip to content

set-a-database-to-single-user-mode.md

Latest commit

 

History

History
92 lines (59 loc) · 4.58 KB

set-a-database-to-single-user-mode.md

File metadata and controls

92 lines (59 loc) · 4.58 KB
title Set a Database to Single-user Mode | Microsoft Docs
ms.custom
ms.date 03/14/2017
ms.prod sql
ms.prod_service database-engine
ms.service
ms.component databases
ms.reviewer
ms.suite sql
ms.technology
database-engine
ms.tgt_pltfrm
ms.topic article
helpviewer_keywords
single-user mode [SQL Server], database option
ms.assetid fb5254eb-b635-4b39-8361-136fd36f2b1f
caps.latest.revision 22
author stevestein
ms.author sstein
manager craigg
ms.workload Active

Set a Database to Single-user Mode

[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md] This topic describes how to set a user-defined database to single-user mode in [!INCLUDEssCurrent] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql]. Single-user mode specifies that only one user at a time can access the database and is generally used for maintenance actions.

In This Topic

Before You Begin

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. For more information, see ALTER DATABASE SET Options (Transact-SQL).

Security

Permissions

Requires ALTER permission on the database.

Using SQL Server Management Studio

To set a database to single-user mode

  1. In Object Explorer, connect to an instance of the [!INCLUDEssDEnoversion], 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.

You can also set the database to Multiple or Restricted access by using this procedure. For more information about the Restrict Access options, see Database Properties (Options Page).

Using Transact-SQL

To set a database to single-user mode

  1. Connect to the [!INCLUDEssDE].

  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 the [!INCLUDEssSampleDBobject] 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 the [!INCLUDEssSampleDBobject] database to be immediately disconnected.

[!code-sqlDatabaseDDL#AlterDatabase8]

See Also

ALTER DATABASE (Transact-SQL)