匯出 SQL Server 登入資訊

MySQL   |  PostgreSQL   |  SQL Server

本文說明如何使用 sp_help_revlogin 預存程序，從 SQL Server 適用的 Cloud SQL 執行個體匯出 SQL Server 登入資訊、安全 ID (SID) 和密碼雜湊。

遷移資料庫、設定資料庫同步處理，或維護讀取副本和災害復原 (DR) 副本時，您必須在目標執行個體上重新建立使用者登入資訊，並使用相符的 SID 和密碼雜湊。這有助於確保資料庫使用者仍對應至相應的伺服器登入資訊，並保留權限。

提示：建議您先匯出並重新建立登入資訊，再還原資料庫。先重新建立具有相符 SID 的登入資訊，可避免資料庫使用者成為孤立使用者。如果登入資訊或密碼在遷移期間有所變更，請在重新導向應用程式流量前，於轉換期間再次執行匯出作業。

SQL Server 適用的 Cloud SQL 提供 sp_help_revlogin 預存程序，可透過 msdb 資料庫產生 Transact-SQL (T-SQL) 指令碼，用於重新建立使用者登入。

事前準備

必要的角色

如要取得設定資料庫標記所需的權限，請要求管理員授予您專案的下列 IAM 角色：

• Cloud SQL 管理員 (roles/cloudsql.admin)
• Cloud SQL 編輯者 (roles/cloudsql.editor)

如要進一步瞭解如何授予角色，請參閱「管理專案、資料夾和組織的存取權」。

這個預先定義的角色具備 cloudsql.instances.update 權限，可設定資料庫標記。

您或許還可透過自訂角色或其他預先定義的角色取得這項權限。

資料庫權限

確認您有權存取預設的 sqlserver SQL Server 使用者角色。

啟用資料庫旗標

如要在 msdb 資料庫中安裝 sp_help_revlogin 預存程序，請在執行個體上啟用 cloud sql enable sp_help_revlogin 資料庫旗標。

Google Cloud 控制台

1. 前往 Google Cloud 控制台的「Cloud SQL Instances」(Cloud SQL 執行個體) 頁面。

   前往 Cloud SQL 執行個體

2. 請按一下執行個體名稱，開啟執行個體的「Overview」 (總覽) 頁面。
3. 按一下 [編輯]。
4. 在「自訂執行個體」部分，展開「旗標」。
5. 按一下「新增旗標」。
6. 從可用旗標清單中選取「cloud sql enable sp_help_revlogin」。
7. 將這個旗標的值設為 on。
8. 按一下 [儲存]。

gcloud CLI

使用 gcloud CLI 啟用旗標：

gcloud sql instances patch INSTANCE_NAME \
    --database-flags="cloud sql enable sp_help_revlogin=on"

將 INSTANCE_NAME 替換為 Cloud SQL 執行個體名稱。

注意：啟用或停用這個旗標不需要重新啟動執行個體。

使用支援的用戶端工具連線

sp_help_revlogin預存程序會使用 T-SQL PRINT 陳述式 (資訊訊息) 生成 CREATE LOGIN指令碼，而非表格結果集 (SELECT 陳述式)。

注意：Cloud SQL Studio 只會顯示表格形式的查詢結果。在 Cloud SQL Studio 中執行 `sp_help_revlogin` 成功，但顯示空白的結果窗格。如要查看及匯出產生的指令碼，請使用 SQL Server Management Studio (SSMS)、Visual Studio Code (搭配 MSSQL 擴充功能) 或 sqlcmd 建立連線。

使用下列任一工具連線至 Cloud SQL 執行個體：

• SQL Server Management Studio (SSMS)：連線至執行個體、執行程序，並在「Messages」分頁中查看產生的指令碼。或者，您也可以在執行查詢前，按 Control+T 鍵將執行輸出模式切換為「結果轉成文字」。
• Visual Studio Code：使用 MSSQL 擴充功能連線、執行程序，並在「Messages」分頁中查看產生的指令碼。

• sqlcmd 公用程式：連線至執行個體，並將產生的指令碼直接輸出至 SQL 檔案：

  sqlcmd -S INSTANCE_IP \
      -U USERNAME \
      -P PASSWORD -d msdb \
      -Q "EXEC dbo.sp_help_revlogin" -o output_logins.sql
  

  更改下列內容：

  • INSTANCE_IP：Cloud SQL 執行個體的 IP 位址。
  • USERNAME：管理資料庫使用者名稱 (例如 sqlserver)。
  • PASSWORD：資料庫使用者密碼。

使用 sp_help_revlogin 匯出登入項目

連線至 msdb 資料庫，然後執行 sp_help_revlogin 預存程序。

• 如要匯出所有客戶登入記錄，請按照下列步驟操作：

  EXEC msdb.dbo.sp_help_revlogin;
  

• 如要匯出特定登入資訊，請按照下列步驟操作：

  EXEC msdb.dbo.sp_help_revlogin
      @login_name = 'LOGIN_NAME';
  

  將 LOGIN_NAME 替換為要匯出的登入名稱。

在目的地執行個體上重新建立登入資料

1. 從查詢輸出內容複製產生的 CREATE LOGIN 陳述式。
2. 連線至目的地 SQL Server 執行個體。
3. 在查詢視窗中或使用 sqlcmd 執行產生的陳述式。

產生的陳述式會在目的地執行個體上建立登入項目，並保留原始 SID、預設資料庫和密碼雜湊。如要進一步瞭解在執行個體之間轉移登入資訊時的注意事項，請參閱 Microsoft 說明文件「Transferring logins and passwords between instances of SQL Server」(在 SQL Server 執行個體之間轉移登入資訊和密碼)。

與唯讀副本同步登入

Cloud SQL 建立唯讀副本或災難復原 (DR) 副本時，會從主要執行個體複製現有登入資訊。不過，在建立副本後於主要執行個體建立的登入資訊，不會自動複製到副本。

雖然唯讀副本上的使用者資料庫是唯讀，但您可以連線至唯讀副本，並在副本的 master 資料庫中執行 CREATE LOGIN 陳述式 (由 sp_help_revlogin 產生)。

建議您定期從主要執行個體匯出新登入項目，並在唯讀備用資源和 DR 備用資源上重新建立這些項目。例行同步作業可確保使用者能通過驗證來讀取副本，且應用程式可在切換、副本容錯移轉或副本升級後立即重新連線。

限制和排除的登入方式

sp_help_revlogin會自動從匯出作業中排除下列登入類型：

• Google Cloud 服務帳戶和內部管理帳戶。
• 內部 SQL Server 系統帳戶 (登入名稱前置字元為 ##)。
• 指派給 sysadmin 固定伺服器角色的登入。
• 指派給受限管理伺服器角色的登入。

停用資料庫旗標

如果不再需要預存程序，請將旗標設為 off (或從執行個體中移除旗標)：

Google Cloud 控制台

1. 前往 Google Cloud 控制台的「Cloud SQL Instances」(Cloud SQL 執行個體) 頁面。

   前往 Cloud SQL 執行個體

2. 請按一下執行個體名稱，開啟執行個體的「Overview」 (總覽) 頁面。
3. 按一下 [編輯]。
4. 在「自訂執行個體」部分，展開「旗標」。
5. 找出「cloud sql enable sp_help_revlogin」，並將值設為「off」 (或點選「刪除」 移除旗標)。
6. 按一下 [儲存]。

gcloud CLI

gcloud sql instances patch INSTANCE_NAME \
    --database-flags="cloud sql enable sp_help_revlogin=off"

設為 off 或移除時，Cloud SQL 會自動從 msdb 資料庫捨棄 dbo.sp_help_revlogin

後續步驟

• 如要進一步瞭解 SQL Server 使用者，請參閱「SQL Server 使用者」。
• 如要進一步瞭解如何設定資料庫旗標，請參閱「設定資料庫旗標」。
• 如要進一步瞭解支援的預存程序，請參閱「SQL Server 預存程序」。
• 如要進一步瞭解如何轉移登入和密碼，請參閱「在 SQL Server 執行個體之間轉移登入和密碼」。