SET SINGLE_USER WITH ROLLBACK IMMEDIATE 僅在帳戶是數據庫使用者時斷開會話

以下程式碼創建一個能夠恢復數據庫的使用者：

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 時會被阻止
• 為什麼在數據庫中創建使用者可以解決這個問題？

正如 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您範例中的命令確實會引發錯誤，但您不會檢查它，或者在繼續還原之前測試數據庫是否處於單使用者模式。

引用自：https://dba.stackexchange.com/questions/311122