将数据库设置为单用户模式Set a Database to Single-user Mode

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本主题说明了如何使用 SQL Server 2017SQL Server 2017SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中将用户定义数据库设置为单用户模式。This topic describes how to set a user-defined database to single-user mode in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. 单用户模式指定一次只有一个用户可访问数据库,该模式通常用于维护操作。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

  • 在将数据库设置为 SINGLE_USER 之前,应验证 AUTO_UPDATE_STATISTICS_ASYNC 选项是否设置为 OFF。Before you set the database to SINGLE_USER, verify that the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. 在此选项设置为 ON 时,用于更新统计信息的后台线程将与数据库建立连接,您将无法以单用户模式访问数据库。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. 有关详细信息,请参阅 ALTER DATABASE SET 选项 (Transact-SQL)For more information, see ALTER DATABASE SET Options (Transact-SQL).

SecuritySecurity

权限Permissions

需要对数据库拥有 ALTER 权限。Requires ALTER permission on the database.

使用 SQL Server Management StudioUsing SQL Server Management Studio

将数据库设置为单用户模式To set a database to single-user mode

  1. 对象资源管理器中,连接到 SQL Server 数据库引擎SQL Server Database Engine的实例,然后展开该实例。In Object Explorer, connect to an instance of the SQL Server 数据库引擎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.

还可以使用此过程将数据库设置为“多用户”访问或“限制”访问。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).

使用 Transact-SQLUsing Transact-SQL

将数据库设置为单用户模式To set a database to single-user mode

  1. 连接到 数据库引擎Database EngineConnect 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. 此示例将数据库设置为 SINGLE_USER 模式,以获得独占访问权。This example sets the database to SINGLE_USER mode to obtain exclusive access. 然后,该示例将 AdventureWorks2012AdventureWorks2012 数据库的状态设置为 READ_ONLY ,并将对数据库的访问权返回给所有用户。在第一个 WITH ROLLBACK IMMEDIATE 语句中指定终止选项 ALTER DATABASEThe example then sets the state of the AdventureWorks2012AdventureWorks2012 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. 这将导致所有未完成事务都将被回滚,并将立刻断开 AdventureWorks2012AdventureWorks2012 示例数据库的所有其他连接。This will cause all incomplete transactions to be rolled back and any other connections to the AdventureWorks2012AdventureWorks2012 database to be immediately disconnected.

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

另请参阅See Also

ALTER DATABASE (Transact-SQL)ALTER DATABASE (Transact-SQL)