SQL Server Single-user Mode

背景

系统管理员可能会由于下列原因之一失去对 SQL Server 实例的访问权限：

• 作为 sysadmin 固定服务器角色成员的所有登录名都已经被误删除。

• 作为 sysadmin 固定服务器角色成员的所有 Windows 组都已经被误删除。

• 作为 sysadmin 固定服务器角色成员的登录名用于已经离开公司或者无法找到的个人。

• sa 帐户被禁用或者没有人知道密码。

作为系统管理员如何可以重新获得对 SQL Server 数据库引擎的访问权限？可以让您重新获得访问权限的一种方法是重新安装 SQL Server 并将所有数据库附加到新实例。这种解决方案很耗时，并且若要恢复登录名，可能还需要从备份中还原 master 数据库。如果 master 数据库的备份较旧，则它可能未包含所有信息。如果 master 数据库的备份较新，则它可能与前一个实例具有同样的登录名；因此管理员仍将被锁定。

解决方法

在单用户模式下启动 SQL Server实例

使用 -m 或 -f 选项在单用户模式下启动 SQL Server 的实例。计算机的本地 Administrators 组的任何成员都可以随后作为 sysadmin 固定服务器角色的成员连接到 SQL Server 实例。

在单用户模式下启动 SQL Server 实例时，请注意下列事项：

• 只有一个用户可以连接到服务器。

• 不执行 CHECKPOINT 进程。默认情况下，启动时自动执行此进程。

注意：在单用户模式下启动 SQL Server 实例时，请首先停止 SQL Server Agent 服务。否则，SQL Server 代理可能会首先连接，并阻止您作为第二个用户连接

当您将 -m 选项与 sqlcmd 或 SQL Server Management Studio 一起使用时，可以将连接限制为指定的客户端应用程序。例如，-m"sqlcmd" 将连接限制为单个连接并且该连接必须将自身标识为 sqlcmd 客户端程序。当您正在单用户模式下启动 SQL Server 并且未知的客户端应用程序正在占用这个唯一的可用连接时，使用此选项。若要通过 Management Studio 中的查询编辑器进行连接，请使用 -m"Microsoft SQL Server Management Studio - Query"。

Use SQL Server Configuration Manager to configure startup options to be used each time the Database Engine starts. For a list of startup options, see Using the SQL Server Service Startup Options.

SQL Server Configuration Manager writes startup parameters to the registry. They take effect upon the next startup of the Database Engine.

Note: On a cluster, changes must be made on the active server while SQL Server is online, and will take effect when the Database Engine is restarted. The registry update of the startup options on the other node will occur upon the next failover.

To configure startup options

1. In SQL Server Configuration Manager, click SQL Server Services.

2. In the right pane, right-click SQL Server (<instance_name>), and then click Properties.

3. On the Advanced tab, in the Startup Parameters box, type the parameters separated by semicolons (;).

   For example, to start in single-user mode, insert -m; in front of the existing startup options, and then restart the database. (When you start SQL Server in single-user mode, first stop SQL Server Agent. Otherwise, SQL Server Agent might connect first and prevent you from connecting as a second user.)

Important: After you are finished using single-user mode, you must remove the -m; from the Startup Parameters box before you can restart the server instance in the normal multi-user mode

      4. Click OK.

      5. Restart the Database Engine.

Set a Database to Single-user Mode

1. Using SQL Server Management Studio 

To set a database to single-user mode

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.

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).

2. Using Transact-SQL

To set a database to single-user mode

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 the AdventureWorks2012 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 AdventureWorks2012 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