HOW TO：在 SQL Server 執行個體之間傳輸登入和密碼

server 概要在將資料庫移動到新伺服器後，使用者可能無法登入到新伺服器。相反，他們會收到下面的錯誤資訊：
Msg 18456, Level 16, State 1
Login failed for user '%ls'.

您必須將登入和密碼傳輸到新伺服器。本文介紹如何向新伺服器傳輸登入和密碼。

返回頁首
如何在正運行 SQL Server 7.0 的伺服器之間傳輸登入和密碼SQL Server 7.0 資料轉換服務 (DTS) 對象傳輸功能可在兩台伺服器之間傳輸登入和使用者，但它不傳輸 SQL Server 驗證登入的密碼。要從一台運行 SQL Server 7.0 的伺服器向另一台運行 SQL Server 7.0 的伺服器傳輸登入和密碼，請按照本文“在 Master 資料庫中建立和運行預存程序”一節中的說明操作。您將在原始伺服器上建立 sp_help_revlogin 預存程序。此過程將產生一個指令碼，您可以在目標伺服器上運行該指令碼，以重新建立帶有原始安全標識號 (SID) 的登入，並保留當前的密碼。

返回頁首

如何從 SQL Server 7.0 向 SQL Server 2000 或者在正運行 SQL Server 2000 的伺服器之間傳輸登入和密碼要從 SQL Server 7.0 伺服器向 SQL Server 2000 的一個執行個體或者在 SQL Server 2000 的兩個執行個體之間傳輸登入和密碼，可以使用 SQL Server 2000 中新的 DTS Package Transfer Logins Task（DTS 包傳輸登入任務）。要使用此任務，請執行以下步驟：

1.串連到 SQL Server 2000 目標伺服器，移動到 SQL Server 企業管理器中的資料轉換服務，展開此檔案夾，按右鍵本地程式包，然後單擊新增程式包。2.在 DTS 程式包設計器開啟後，單擊任務菜單上的傳輸登入任務。根據需要完成有關源、目標和登入選項卡的資訊。

重要說明：SQL Server 2000 目標伺服器不能運行 64 位元版本的 SQL Server 2000。64 位元版本 SQL Server 2000 的 DTS 組件不可用。如果要從其他電腦上的 SQL Server 執行個體中匯入登入，您的 SQL Server 執行個體必須在域帳戶下運行才能完成此任務。

注意：您可以使用 DTS 方法或本文“在 Master 資料庫中建立和運行預存程序”一節中的指令碼，從 SQL Server 7.0 向 SQL Server 2000 或者在 SQL Server 2000 的執行個體之間傳輸登入。DTS 方法將傳輸密碼，但不傳輸原始 SID。如果登入不是使用原始 SID 建立的，而且使用者資料庫也被傳輸到一台新伺服器，則該資料庫使用者將被從該登入中孤立出去。要傳輸原始 SID 並迴避孤立使用者，請使用本文下一節中的指令碼代替 DTS 方法。返回頁首

在 Master 資料庫中建立和運行預存程序請查看本文末尾的備忘，以瞭解有關下列步驟的重要訊息。
1.在源 SQL Server 上運行以下指令碼。此指令碼可在 master 資料庫中建立名稱分別為 sp_hexadecimal 和 sp_help_revlogin 的兩個預存程序。請在建立完過程之後繼續執行第 2 步。

注意：下面的過程取決於 SQL Server 系統資料表。這些表的結構在 SQL Server 的不同版本之間可能會有變化，請不要直接從系統資料表中選擇。

----- Begin Script, Create sp_help_revlogin procedure -----USE masterGOIF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimalGOCREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar(256) OUTPUTASDECLARE @charvalue varchar(256)DECLARE @i intDECLARE @length intDECLARE @hexstring char(16)SELECT @charvalue = '0x'SELECT @i = 1SELECT @length = DATALENGTH (@binvalue)SELECT @hexstring = '0123456789ABCDEF'WHILE (@i <= @length)BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1ENDSELECT @hexvalue = @charvalueGOIF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revloginGOCREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL ASDECLARE @name sysnameDECLARE @xstatus intDECLARE @binpwd varbinary (256)DECLARE @txtpwd sysnameDECLARE @tmpstr varchar (256)DECLARE @SID_varbinary varbinary(85)DECLARE @SID_string varchar(256)IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT sid, name, xstatus, password FROM master..sysxlogins WHERE srvid IS NULL AND name <> 'sa'ELSE DECLARE login_curs CURSOR FOR SELECT sid, name, xstatus, password FROM master..sysxlogins WHERE srvid IS NULL AND name = @login_nameOPEN login_cursFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwdIF (@@fetch_status = -1)BEGIN PRINT 'No login(s) found.' CLOSE login_curs DEALLOCATE login_curs RETURN -1ENDSET @tmpstr = '/* sp_help_revlogin script 'PRINT @tmpstrSET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'PRINT @tmpstrPRINT ''PRINT 'DECLARE @pwd sysname'WHILE (@@fetch_status <> -1)BEGIN IF (@@fetch_status <> -2) BEGIN PRINT '' SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr IF (@xstatus & 4) = 4 BEGIN -- NT authenticated account/group IF (@xstatus & 1) = 1 BEGIN -- NT login is denied access SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + '''' PRINT @tmpstr END ELSE BEGIN -- NT login has access SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + '''' PRINT @tmpstr END END ELSE BEGIN -- SQL Server authentication IF (@binpwd IS NOT NULL) BEGIN -- Non-null password EXEC sp_hexadecimal @binpwd, @txtpwd OUT IF (@xstatus & 2048) = 2048 SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')' ELSE SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')' PRINT @tmpstrEXEC sp_hexadecimal @SID_varbinary,@SID_string OUT SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = ' END ELSE BEGIN -- Null passwordEXEC sp_hexadecimal @SID_varbinary,@SID_string OUT SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = ' END IF (@xstatus & 2048) = 2048 -- login upgraded from 6.5 SET @tmpstr = @tmpstr + '''skip_encryption_old''' ELSE SET @tmpstr = @tmpstr + '''skip_encryption''' PRINT @tmpstr END END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd ENDCLOSE login_cursDEALLOCATE login_cursRETURN 0GO ----- End Script -----
2.在建立 sp_help_revlogin 預存程序之後，請從原始伺服器上的查詢分析器中運行 sp_help_revlogin 過程。sp_help_revlogin 預存程序可同時用於 SQL Server 7.0 和 SQL Server 2000。sp_help_revlogin 預存程序的輸出是登入指令檔，該指令碼可建立帶有原始 SID 和密碼的登入。儲存輸出，然後將其粘貼到目標 SQL Server 上的查詢分析器中，並運行它。例如：
EXEC master..sp_help_revlogin
返回頁首

備忘•在目標 SQL Server 上運行輸出指令碼之前，請認真查看此指令碼。如果必須將登入傳輸到與 SQL Server 源執行個體不在同一個域中的 SQL Server 執行個體，請編輯由 sp_help_revlogin 過程產生的指令碼，並在 sp_grantlogin 語句中將網域名稱替換為新的網域名稱。由於在新域中被授予訪問權的整合登入與原域中的登入具有不同的 SID，因此資料庫使用者將被從這些登入中孤立出去。要解決這些孤立使用者，請參見以下項目符號項中引用的文章。如果在同一個域中的 SQL Server 執行個體之間傳輸整合登入，則會使用相同的 SID，而且使用者不太可能被孤立。•在移動登入之後，使用者將不再具有訪問已被同時移動的資料庫的許可權。此問題稱為“孤立使用者”。如果嘗試將訪問此資料庫的許可權授予該登入，則可能會失敗，這表明該使用者已存在：
Microsoft SQL-DMO (ODBC SQLState:42000) Error 15023:User or role '%s' already exists in the current database.有關如何將登入映射到資料庫使用者以解決孤立的 SQL Server 登入和整合登入的說明，請參見以下 Microsoft 知識庫文章： 240872 HOW TO：在 SQL 伺服器之間移動資料庫時如何解決許可權問題
有關使用 sp_change_users_login 預存程序逐個解決孤立使用者（僅能解決從標準 SQL 登入中孤立出去的使用者）的說明，請參見以下 Microsoft 知識庫文章： 274188 PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete •如果傳輸登入和密碼是向運行 SQL Server 的新伺服器移動資料庫的一部分，請參見以下 Microsoft 知識庫文章，以瞭解對所涉及的工作流程和步驟的說明： 314546 HOW TO: Move Databases Between Computers That Are Running SQL Server •能夠這樣做的原因在於：sp_addlogin 系統預存程序中的 @encryptopt 參數允許通過使用加密密碼來建立登入。有關此過程的更多資訊，請參見 SQL Server 聯機圖書中的“sp_addlogin (T-SQL)”主題。•預設情況下，只有 sysadminfixed 伺服器角色的成員可以從 sysxlogins 表中進行選擇。除非 sysadmin 角色的成員授予了必要的許可權，否則終端使用者將無法建立或運行這些預存程序。•此方法不會嘗試傳輸特定登入的預設資料庫資訊，因為預設資料庫並不始終存在於目標伺服器中。要為某個登入定義預設資料庫，您可以使用 sp_defaultdb 系統預存程序，並將登入名稱和預設資料庫作為參數傳遞給該過程。有關使用此過程的更多資訊，請參見 SQL Server 聯機圖書中的“sp_defaultdb”主題。•在 SQL Server 執行個體之間傳輸登入的過程中，如果原始伺服器的排序次序不區分大小寫，而目標伺服器的排序次序區分大小寫，則在將登入傳輸到目標伺服器後，必須在密碼中用大寫形式輸入所有字母字元。如果原始伺服器的排序次序區分大小寫，而目標伺服器的排序次序不區分大小寫，則無法通過使用本文概述的過程傳輸的登入進行登入，除非原始密碼不包含字母字元，或者原始密碼中的所有字母字元都是大寫字元。如果兩個伺服器都區分大小寫或者都不區分大小寫，則不會出現此問題。這是 SQL Server 處理密碼的方式所帶來的副作用。有關更多資訊，請參見 SQL Server 7.0 聯機圖書中的“Effect on Passwords of Changing Sort Orders”（更改排序次序對密碼的影響）主題。•當在伺服器上運行 sp_help_revlogin 指令碼的輸出時，如果該伺服器已經定義了一個登入，且該登入名稱與指令碼輸出中的某個登入的名稱相同，則在執行 sp_help_revlogin 指令碼的輸出時，可能會看到下面的錯誤資訊：


Server:Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 56
The login 'test1' already exists.

同樣，如果此伺服器上存在其他登入，且其 SID 值與您要嘗試添加的登入相同，則會收到以下錯誤資訊：


Server:Msg 15433, Level 16, State 1, Procedure sp_addlogin, Line 93
Supplied parameter @sid is in use.

因此，您必須仔細查看這些命令的輸出，檢查 sysxlogins 表的內容，並相應地解決這些錯誤。•特定登入的 SID 值被用作在 SQL Server 中實現資料庫層級訪問的基礎。因此，如果同一登入在該資料庫層級（在該伺服器上的兩個不同資料庫中）有兩個不同的 SID 值，則此登入將僅能訪問其 SID 與該登入的 syslogins 中的值相匹配的資料庫。如果所討論的兩個資料庫已從兩個不同的伺服器合并在一起，則可能出現這種情形。要解決此問題，需要使用 sp_dropuser 預存程序從具有不匹配 SID 的資料庫中手動刪除所討論的登入，然後再使用 sp_adduser 預存程序添加它。