主页 / dba / 问题 / 311122
Accepted
SEarle1986
Asked: 2022-04-20 14:25:36 +0800 CST

SET SINGLE_USER WITH ROLLBACK IMMEDIATE 仅在帐户是数据库用户时断开会话

  • 772

以下代码创建一个能够恢复数据库的用户：

CREATE LOGIN RestoreUser WITH PASSWORD = 'MyPassword'
ALTER SERVER ROLE dbcreator ADD MEMBER RestoreUser

这工作正常，用户可以恢复数据库，但是如果我想将数据库设置为SINGLE_USER并回滚任何现有连接，恢复命令将被阻止并失败：

第 1 节

/* SELECT from a table and leave the SSMS window open, leaving a sleeping SPID */
USE AdventureWorks2014
SELECT * FROM Person.Person

第 2 节

EXECUTE AS LOGIN = 'RestoreUser'

ALTER DATABASE AdventureWorks2014 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE AdventureWorks2014 FROM DISK = 'C:\Test\AW14.bak' WITH REPLACE

REVERT

最终，会话 2 超时

Msg 5061, Level 16, State 1, Line 3
ALTER DATABASE failed because a lock could not be placed on database 'AdventureWorks2014'. Try again later.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Msg 3101, Level 16, State 1, Line 5
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

以下sp_whoisactive屏幕截图显示该命令被阻止：

在此处输入图像描述

会话 59的sp_whoisactive锁定报告显示

<Database name="AdventureWorks2014">
  <Locks>
    <Lock request_mode="X" request_status="CONVERT" request_count="1" />
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
    <Lock request_mode="U" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="(null)">
      <Locks>
        <Lock resource_type="DATABASE.BULKOP_BACKUP_DB" request_mode="U" request_status="GRANT" request_count="1" />
        <Lock resource_type="DATABASE.BULKOP_BACKUP_LOG" request_mode="S" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

SET SINGLE_USER的Microsoft 文章指出

为了快速获得独占访问，代码示例使用了 WITH ROLLBACK IMMEDIATE 终止选项。这将导致所有未完成的事务被回滚，并且与 AdventureWorks2012 数据库的任何其他连接立即断开。

和

需要对数据库的 ALTER 权限。

我的RestoreUser帐户具有正确的权限（dbcreator服务器角色授予ALTER ANY DATABASE权限）但我不知道为什么命令被阻止，第一个引号表明所有其他连接都将被断开。

我也同意ALTER ANY CONNECTION了，RestoreUser但这并没有帮助。

我发现确实解决了问题，是否RestoreUser正在恢复数据库中的用户（不需要数据库中的任何权限）

CREATE LOGIN RestoreUser WITH PASSWORD = 'ABC@123'
ALTER SERVER ROLE dbcreator ADD MEMBER RestoreUser

USE AdventureWorks2014
CREATE USER RestoreUser

编码

EXECUTE AS LOGIN = 'RestoreUser'

ALTER DATABASE AdventureWorks2014 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE AdventureWorks2014 FROM DISK = 'C:\Test\AW14.bak' WITH REPLACE

REVERT

然后运行而不会被睡眠会话阻塞。

我的问题是

  • 为什么SET SINGLE_USER WITH ROLLBACK IMMEDIATE代码应该杀死并回滚所有打开的 SPID 时会被阻止
  • 为什么在数据库中创建用户可以解决这个问题？
restore sql-server-2016

1 个回答

  1. Best Answer

    正如 Dan Guzman 最初指出的那样，sp_WhoIsActive输出指示RESTORE被阻止，而不是ALTER DATABASE命令。即使是这样：

    如果SET SINGLE_USER命令是uncontended，则没有问题。连接获得单SESSION级共享数据库锁，防止其他任何人连接到数据库，生活是美好的。

    如果存在争用（与数据库的另一个连接），则会检查执行命令的安全主体是否可以连接到数据库。

    如果此检查失败，则ALTER DATABASE命令失败并返回如下错误消息：

    消息 5061，级别 16，状态 1，第 10 行 ALTER DATABASE 失败，因为无法在数据库“AdventureWorks2017”上放置锁。稍后再试。
    消息 5069，级别 16，状态 1，第 10 行 ALTER DATABASE 语句失败。

    在以下情况下检查成功：

    • 校长有CONNECT ANY DATABASE权限；或者
    • 主体在数据库中有相关用户；或者
    • 数据库中的来宾用户具有CONNECT权限。

    我不知道为什么这个检查存在，或者为什么它只在有阻塞连接时才会发生。可能有充分的理由，或者可能是不准确或过时的测试。

    在某些方面，当主体无权连接到数据库时，会话可以获取与单个用户SESSION关联的单个共享数据库锁，这很奇怪。

    另请注意，如果您尝试将数据库设置为MULTI_USER当它已经处于该状态并且有其他用户连接到数据库时，则会出于相同的原因发生相同的错误。

    另一方面，有人可能会争辩说，这ALTER ANY DATABASE并不能完全满足您引用ALTER的特定数据库权限的要求。拥有ALTER数据库权限意味着该数据库中的用户，因为该权限只能分配给用户，而不是登录。这很有说服力，但没有解释为什么只在发生争用时才应该对其进行测试。

    除此之外，ALTER DATABASE您示例中的命令确实会引发错误，但您不会检查它，或者在继续还原之前测试数据库是否处于单用户模式。

    • 3

相关问题