如何解決在運行 SQL Server 的伺服器之間移動資料庫時的許可權問題

概要

本文描述如何映射標準登入和整合登入來解決在運行 SQL Server 的伺服器之間移動資料庫時的許可權問題。

更多資訊

當您將資料庫從一個運行 SQL Server 的伺服器移到另一個運行 SQL Server 的伺服器時，master 資料庫中登入的安全標識號 (SID) 與使用者資料庫中使用者的 SID 可能不匹配。預設情況下，SQL Server 7.0、SQL Server 2000 和 SQL Server 2005 會提供 sp_change_users_login 系統預存程序來映射這些不匹配的使用者。但是，sp_change_users_login 預存程序僅能用於映射標準的 SQL Server 登入，而且需要一次對一個使用者執行這些映射。有關 sp_change_users_login 預存程序的更多資訊，請參閱 SQL Server 7.0、SQL Server 2000 和 SQL Server 2005 聯機叢書中的“sp_change_users_login”主題。
在 SQL Server 7.0 或更高版本中，您可以使用 SID 來維護 master 資料庫中的登入和使用者資料庫中的使用者之間的映射。此映射是維護使用者資料庫中登入的正確許可權所必需的。如果丟失此映射，登入將發生許可權問題，其中包括但不限於以下問題：

•如果新伺服器上不存在 SQL Server 登入，而使用者嘗試登入，該使用者可能會收到以下錯誤訊息：

Server:Msg 18456, Level 16, State 1
Login failed for user '%ls'.

•如果新伺服器上存在 SQL Server 登入，但 master 資料庫中的 SID 與使用者資料庫中的 SID 不相同，則使用者可以成功登入到 SQL Server；但是，當使用者嘗試訪問該資料庫時，可能會收到以下錯誤訊息：

Server:Msg 916, Level 14, State 1, Line1
Server user '%.*ls' is not a valid user in database '%.*ls'.

注意：在 SQL Server 2005 中，使用者可能會收到以下錯誤訊息：

伺服器使用者“%s”不是資料庫“%s”中的有效使用者。請先將該使用者帳戶添加到資料庫中。

有關 SQL Server 7.0 安全模型的更多資訊，請參閱“Microsoft SQL Server 7.0 Security”（Microsoft SQL Server 7.0 安全性）白皮書。要查看此白皮書，請訪問下面的 Microsoft 網站：

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/sql7security.asp

(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/sql7security.asp)

有關 SQL Server 2000 安全模型的更多資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：

322712 (http://support.microsoft.com/kb/322712/) Microsoft SQL Server 2000 S322712 安全功能和最佳做法

要下載 Mapsids.exe 檔，請使用下面的下載連結：

Mapsids.exe
http://download.microsoft.com/download/sqlsvr2000/utility/5.0/win98me/en-us/Mapsids.exe

(http://download.microsoft.com/download/sqlsvr2000/utility/5.0/win98me/en-us/mapsids.exe)

限制

•如果 sysusers 表中有使用者沒有以電腦名稱或擁有對象的網域名稱作為首碼，而應用程式中使用包含兩個部分的名稱 username.objectname 引用了這些對象，則應用程式可能會中斷，因為當 sp_sidmap 預存程序在 sysxlogins 表中出現時，它會以電腦名稱或網域名稱作為首碼重新命名這些使用者。要解決此問題，請在 sp_sidmap 預存程序完成後，將 sysusers 表中受影響的使用者重新命名為它們原來的名稱，或者與您的主要支援供應商聯絡。

•本文未涉及別名。您必須手動管理別名。

•如果新的 SQL Server 服務器上不存在標準的 SQL Server 登入，則會添加密碼為空白的登入。因此，您可能需要更改這些登入的密碼。

•如果使用者是在使用者資料庫中建立的，而且該使用者的名稱不同於 sysxlogins 表中顯示的名稱，則無法知道該使用者的相應登入。因此，在執行 sp_sidmap 預存程序之前：

1.將該使用者擁有的所有對象轉移到一個臨時資料庫。

2.刪除該使用者，添加具有正確名稱的使用者，然後再移回該使用者的所有對象。

•如果使用者不具有對應的登入，而且也沒有以本機電腦名或網域名稱作為首碼，您將收到有關該使用者的一則訊息。該訊息指出，需要首先在 Windows 層級添加該使用者，再將其作為登入添加到 SQL Server；然後，您必須要再次執行 sp_sidmap 預存程序。

•如果使用者以網域名稱或本地 Windows 伺服器名作為首碼，但 sysxlogins 表中不存在相應的登入，該預存程序會嘗試將其作為新登入添加到 SQL Server。如果該 Windows 使用者不存在，則將在結果視窗中產生一條輸出訊息，然後在它首次添加該 Windows 使用者後手動建立登入。

•如果 sysusers 表中的某一使用者有多個登入，您將在結果檔案中看到一則輸出訊息，它會列出具有相同使用者名稱的所有登入。此時，您必須手動幹預，以確保該使用者僅對應於一個登入。
樣本：如果 sysusers 表中有一個名稱為“johndoe”的使用者，而 sysxlogins 表中有名為諸如“Test\johndoe”和“Test2\johndoe”的登入，則當運行預存程序時，您將收到一條訊息，指出其中一個使用者具有多個登入，系統管理員必須從中選擇一個。只有在這種情況下，您才必須運行本文提供的第二個預存程序 sp_prefix_sysusersname。另外，Readme.txt 檔案中也詳細介紹了這種情況。

映射標準登入和整合登入

當您將資料庫從一個運行 SQL Server 的伺服器移到另一個運行 SQL Server 的伺服器後，請按照下列步驟操作，以盡量減少使用者幹預：

1.確保對於資料庫的 sysusers 表中的每一個使用者，master 資料庫的 sysxlogins 表中都有一個登入。
注意：要添加標準 SQL Server 登入，請參閱 SQL Server 聯機叢書中的“sp_addlogin”主題。要添加整合的 SQL Server 登入，請參閱 SQL Server 聯機叢書中的“sp_grantlogin”主題。

2.下載 MapSids.exe 檔，然後解壓縮 Sp_sidmap.sql 和 Readme.txt 檔案。

3.以系統管理員身份登入到運行 SQL Server 的伺服器，然後在使用者資料庫中運行 Sp_sidmap.sql 檔案。運行 Sp_sidmap.sql 檔案會建立兩個預存程序：sp_sidmap 和 sp_prefix_sysusersname。

4.確保除了運行這些預存程序的使用者之外，沒有其他任何使用者訪問該資料庫。

5.確保“查詢分析器”視窗以文字格式設定（而不是網格格式）顯示結果。為此，請按 Ctrl^T 鍵，或者單擊查詢，然後單擊“文本顯示結果”。這是非常重要的，它使您可以在一個視窗中查看結果和資訊性訊息，並將輸出儲存到文字檔中。稍後可能需要使用該檔案來解析某些映射。

6.由於您無法驗證參數是否已正確傳遞，因此請確保將參數正確地傳遞到 sp_sidmap 預存程序：

EXEC sp_SidMap @old_domain = old_domain_name,@new_domain = new_domain_name,@old_server = old_server_name,@new_server = new_server_name

適當地替換新舊網域名稱和伺服器名的值。

7.將結果儲存在一個檔案中，然後按照 Readme.txt 檔案中提供的指導操作。
注意：當您運行這些預存程序時，資料庫中唯一變化的表是 sysusers 表。如果您需要返回到開始時的狀態，請從備份中還原資料庫或者重新附加資料庫。

參考

有關更多資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：

274188 (http://support.microsoft.com/kb/274188/) PRB：聯機叢書中的“孤立使用者疑難解答”主題不完整

246133 (http://support.microsoft.com/kb/246133/) 如何在 SQL Server 執行個體之間傳輸登入和密碼

168001 (http://support.microsoft.com/kb/168001/) 還原資料庫後資料庫上的使用者登入和許可權可能不正確

298897 (http://support.microsoft.com/kb/298897/) 樣本：Mapsids.exe 有助於在移動資料庫時在使用者資料庫和 master 資料庫之間映射 SID

from: http://support.microsoft.com/kb/240872/