當系統管理員遭到鎖定時連線到 SQL Server

發行項
02/29/2024

適用於：SQL Server

此文章描述如果您遭到鎖定，如何以系統管理員身分重新取得 SQL Server 資料庫引擎的存取權。系統管理員可能因為下列其中一個原因而失去 SQL Server 執行個體的存取權：

屬於 sysadmin 固定伺服器角色之成員的所有登入都因為錯誤而遭到移除。
屬於 sysadmin 固定伺服器角色之成員的所有 Windows 群組都因為錯誤而遭到移除。
屬於 sysadmin 固定伺服器角色之成員的登入供已經離開公司或沒有空的個人使用。
sa 帳戶已遭到停用或沒有人知道密碼。

解決方案

為了解決您的存取權問題，建議您以單一使用者模式啟動 SQL Server 的執行個體。此模式可防止當您嘗試重新取得存取權時發生其他連線。從這裡，您可以連線到 SQL Server 執行個體，並將登入新增至系統管理員 (sysadmin) 伺服器角色。逐步指示一節會提供此解決方案的詳細步驟。

您可以從命令列中使用 -m 或 -f 選項，以單一使用者模式啟動 SQL Server 的執行個體。電腦本機系統管理員群組的所有成員接著就能利用系統管理員 (sysadmin) 固定伺服器角色的成員身分，連線到 SQL Server 的執行個體。

當您以單一使用者模式啟動執行個體時，請停止 SQL Server Agent 服務。否則，SQL Server Agent 可能會先連線，取得與伺服器的唯一可用連線，並封鎖您登入。

在您能夠登入之前，未知的用戶端應用程式也可能取得唯一可用的連線。為了避免發生這種情況，您可以使用 -m 選項，後面接著應用程式名稱，以將連線限制為來自指定應用程式的單一連線。例如，使用 -mSQLCMD 啟動 SQL Server，會將連線限制為單一連線，以將自己識別為 sqlcmd 用戶端程式。若要在 Management Studio 中透過查詢編輯器進行連線，請使用 -m"Microsoft SQL Server Management Studio - Query"。

重要

請勿搭配應用程式名稱使用 -m 作為安全性功能。用戶端應用程式會透過連接字串設定來指定應用程式名稱，因此其可利用假的名稱輕鬆地偽造。

下表摘要說明在命令列中，以單一使用者模式啟動執行個體的各種方式。

選項	描述	使用時機
`-m`	將連線限制為單一連線	當沒有其他使用者嘗試連線到執行個體，或您不確定用來連線到執行個體的應用程式名稱時。
`-mSQLCMD`	將連線限制為單一連線，其必須將自己識別為 sqlcmd 用戶端程式	當您計畫使用 sqlcmd 連線到執行個體，而且您想要防止其他應用程式取得唯一可用的連線時。
`-m"Microsoft SQL Server Management Studio - Query"`	將連線限制為單一連線，其必須將自己識別為 Microsoft SQL Server Management Studio - 查詢應用程式。	當您計畫透過 Management Studio 中的查詢編輯器連線到執行個體，而且您想要防止其他應用程式取得唯一可用的連線時。
`-f`	將連線限制為單一連線，並以基本組態來啟動執行個體	當某些其他組態導致您無法啟動時。

逐步指示

如需如何以單一使用者模式啟動 SQL Server 的逐步指示，請參閱以單一使用者模式啟動 SQL Server。

使用 PowerShell

選項 1：透過 Azure Data Studio 直接在可執行筆記本中執行步驟

注意

嘗試開啟此筆記本之前，請先檢查本機電腦上是否已安裝 Azure Data Studio。若要安裝 Azure Data Studio，請前往了解如何安裝 Azure Data Studio。

在 Azure Data Studio 中開啟筆記本

選項 2：手動遵循步驟

以系統管理員的身分開啟並執行 Windows PowerShell 命令
設定服務名稱和 SQL Server 執行個體，以及 Windows 登入變數。以值取代這些值以符合您的環境

如果您有預設執行個體，請使用不含執行個體名稱的 MSSQLSERVER。
```
$service_name = "MSSQL`$instancename"
$sql_server_instance = "machine_name\instance"
$login_to_be_granted_access = "[CONTOSO\PatK]"
```
停止 SQL Server 服務，以便使用下列命令以單一使用者模式重新開機：

如果您有預設執行個體，請使用不含執行個體名稱的 MSSQLSERVER。
```
net stop $service_name
```
現在，以單一使用者模式啟動您的 SQL Server 執行個體，只允許 SQLCMD.exe 連線 (/mSQLCMD)

注意

請務必使用大寫 SQLCMD

如果您有預設執行個體，請使用不含執行個體名稱的 MSSQLSERVER。
```
net start $service_name /f /mSQLCMD
```
使用 sqlcmd 執行 CREATE LOGIN 命令，後面接著 ALTER SERVER ROLE 命令。此步驟假設您已使用屬於本機系統管理員群組成員的帳戶登入 Windows。這會假設您已將網域和登入名稱取代為您想要授與系統管理員成員資格的認證。

如果您有預設執行個體，請使用伺服器的名稱。
```
sqlcmd.exe -E -S $sql_server_instance -Q "CREATE LOGIN $login_to_be_granted_access FROM WINDOWS; ALTER SERVER ROLE sysadmin ADD MEMBER $login_to_be_granted_access; "
```
注意

如果您收到下列錯誤，您必須確定沒有其他 SQLCMD 已連線到 SQL Server：
Sqlcmd: Error: Microsoft ODBC Driver X for SQL Server : Login failed for user 'CONTOSO\BobD'. Reason: Server is in single user mode. Only one administrator can connect at this time..
混合模式 (選擇性)：如果您的 SQL Server 是以混合驗證模式執行，您也可以：
1. 將 Sysadmin 角色成員資格授與SQL 登入。執行程式碼 (例如下列程式碼) 來建立屬於系統管理員 (sysadmin) 固定伺服器角色成員的新 SQL Server 驗證登入。以您選擇的強式密碼取代 ?j8:z$G=JE9。
  
  如果您有預設執行個體，請使用伺服器的名稱。
```
$strong_password = "j8:zG=J?E9"
sqlcmd.exe -E -S $sql_server_instance -Q "CREATE LOGIN TempLogin WITH PASSWORD = '$strong_password'; ALTER SERVER ROLE sysadmin ADD MEMBER TempLogin; "
```
2. 此外，如果您的 SQL Server 是以混合驗證模式執行，而且您想要重設已啟用 sa 帳戶的密碼。您可以使用下列語法來變更 sa 帳戶的密碼。請務必以您選擇的強式密碼取代j8:zG=J?E9：
  
  如果您有預設執行個體，請使用伺服器的名稱。
```
$strong_password = "j8:zG=J?E9"
sqlcmd.exe -E -S $sql_server_instance -Q "ALTER LOGIN sa WITH PASSWORD = $strong_password; "
```
以多使用者模式停止並重新啟動您的 SQL Server 執行個體

如果您有預設執行個體，請使用不含執行個體名稱的 MSSQLSERVER。
```
net stop $service_name
net start $service_name
```

請使用 SQL Server 組態管理員和 Management Studio (SSMS)

這些指示假設：

SQL Server 在 Windows 8 或更高版本上執行。適當地針對舊版 SQL Server 或 Windows 提供些微調整。
SQL Server Management Studio 安裝於電腦上。

以本機系統管理員群組成員身分登入 Windows 時，執行這些指示。

在 Windows [開始] 功能表中，以滑鼠右鍵按一下 SQL Server 組態管理員的圖示，然後選擇 [以系統管理員身分執行]，以將您的系統管理員認證傳遞給組態管理員。
在 SQL Server 組態管理員中，選取位於左側窗格中的 [SQL Server 服務]。在右窗格中，尋找您的 SQL Server 執行個體。 (SQL Server 的預設執行個體會在電腦名稱後面加上 (MSSQLSERVER)。具名執行個體會以 [已註冊的伺服器] 中顯示的相同大寫名稱出現)。以滑鼠右鍵按一下 SQL Server 執行個體，然後選取 [屬性]。
在 [啟動參數] 索引標籤的 [指定啟動參數] 方塊中，輸入 -m，然後選取 [加入]。 (這是虛線，然後接著小寫字母 m)。

如果是某些 SQL Server 的舊版，則沒有 [啟動參數] 索引標籤。在該情況下，請在 [進階] 索引標籤上，按兩下 [啟動參數]。這些參數就會在小的視窗中開啟。請小心不要變更任何現有參數。在結尾處，加上新的參數 ;-m，然後選取 [確定]。 (這是分號，然後接著虛線和小寫字母 m)。
選取 [確定]，在重新啟動的訊息後面，以滑鼠右鍵按一下您的伺服器名稱，然後選取 [重新啟動]。
重新啟動 SQL Server 之後，您的伺服器將處於單一使用者模式。請確定 SQL Server Agent 沒有在執行。如果已啟動，它將會佔用您的唯一連接。
從 Windows [開始] 功能表中，以滑鼠右鍵按一下 Management Studio 的圖示，然後選取 [以系統管理員身分執行]。這樣會將您的系統管理員認證傳遞給 SSMS。

如果是舊版 Windows，[以系統管理員身分執行] 選項會顯示成子功能表。

在某些組態中，SSMS 會嘗試建立許多連接。多重連接將會失敗，因為 SQL Server 處於單一使用者模式。根據您的情況，執行下列其中一個動作。
1. 使用 Windows 驗證 (包含您的系統管理員認證) 透過 [物件總管] 連線。依序展開 [安全性] 和 [登入]，然後按兩下您自己的登入。在 [伺服器角色] 頁面上，選取 [系統管理員]，然後選取 [確定]。
2. 不透過 [物件總管] 連接，而是使用 Windows 驗證 (包含您的系統管理員認證) 透過查詢視窗連接 (如果您沒有透過 [物件總管] 連接，就只能以這種方式連接)。執行程式碼 (例如下列程式碼) 來加入屬於系統管理員 (sysadmin) 固定伺服器角色成員的新 Windows 驗證登入。下列範例會加入名為 CONTOSO\PatK 的網域使用者。
```
CREATE LOGIN [CONTOSO\PatK] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [CONTOSO\PatK];
```
3. 如果您的 SQL Server 是以混合驗證模式執行，請使用 Windows 驗證 (包含您的系統管理員認證) 透過查詢視窗連接。執行程式碼 (例如下列程式碼) 來建立屬於系統管理員 (sysadmin) 固定伺服器角色成員的新 SQL Server 驗證登入。
```
CREATE LOGIN TempLogin WITH PASSWORD = '************';
ALTER SERVER ROLE sysadmin ADD MEMBER TempLogin;
```
  警告
  
  請將 ************ 取代成增強式密碼。
4. 如果您的 SQL Server 是以混合驗證模式執行，而且您想要重設 sa 帳戶的密碼，請使用 Windows 驗證 (包含您的系統管理員認證) 透過查詢視窗連接。您可以使用下列語法來變更 sa 帳戶的密碼。
```
ALTER LOGIN sa WITH PASSWORD = '************';
```
  警告
  
  請將 ************ 取代成增強式密碼。
關閉 Management Studio。
接下來的數個步驟會變更 SQL Server 以回到多使用者模式。在 SQL Server 組態管理員中，選取位於左側窗格中的 [SQL Server 服務]。
在右窗格中，滑鼠右鍵按一下 [SQL Server 的執行個體]，然後選取 [屬性]。
在 [啟動參數] 索引標籤的 [現有參數] 方塊中，選取 -m，然後選取 [移除]。

如果是某些 SQL Server 的舊版，則沒有[啟動參數]索引標籤。在該情況下，請在[進階]索引標籤上，按兩下[啟動參數]。這些參數就會在小的視窗中開啟。移除您先前加入的 ;-m，然後選取 [確定]。
以滑鼠右鍵按一下您的伺服器名稱，然後選取 [重新啟動]。如果您在以單一使用者模式啟動 SQL Server 之前停止了 SQL Server Agent，請務必再次加以啟動。

現在，您應該能夠使用其中一個目前屬於系統管理員 (sysadmin) 固定伺服器角色成員的帳戶進行正常連接。