如何在 SQL Server 的執行個體之間傳送登入和密碼

ENIX.DQ

文章來源：http://support.microsoft.com/kb/246133/zh-tw


在您將資料庫移至新的伺服器之後，使用者可能無法登入新的伺服器。而且，他們會收到下列錯誤訊息：訊息 18456，層級 16，狀態 1
使用者 '%ls' 的登入失敗。



您必須將登入和密碼傳送至新的伺服器。本文將告訴您，如何將登入和密碼傳送至新的伺服器。回此頁最上方
如何在執行 SQL Server 7.0 的伺服器之間傳送登入和密碼SQL Server 7.0 資料轉換服務 (DTS) 的「物件傳送」功能可以在兩部伺服器之間傳送登入和使用者，但不會傳送 SQL Server 驗證登入的密碼。如果要將登入和密碼從執行 SQL Server 7.0 的伺服器，傳送至另一部執行 SQL Server 7.0 的伺服器，請依照＜在不同版本的 SQL Server 之間傳送登入和密碼的完整解決方案＞一節中的步驟執行。回此頁最上方
如何將登入和密碼從 SQL Server 7.0 傳送至 SQL Server 2000，或是在執行 SQL Server 2000 的伺服器之間傳送如果要將登入和密碼從 SQL Server 7.0 伺服器傳送至 SQL Server 2000 的執行個體，或是在兩個 SQL Server 2000 執行個體之間傳送，您可以使用 SQL Server 2000 中的新「DTS 封裝傳送登入工作」(DTS Package Transfer Logins Task)。如果要執行這項操作，請依照下列步驟執行：

• 連線至 SQL Server 2000 目的地伺服器，移至 SQL Server Enterprise Manager 中的「資料轉換服務」，展開資料夾，用滑鼠右鍵按一下 [本機封裝]，然後按一下 [新增封裝]。
• DTS Package Designer 開啟之後，按一下 [工作] 功能表上的 [傳送登入工作]。根據您的情況，完成 [來源]、[目的地] 和 [登入] 索引標籤中的資訊。
  
  重要 SQL Server 2000 目的地伺服器無法執行 64 位元版本的 SQL Server 2000。Microsoft 並未提供 64 位元版本的 SQL Server 2000 適用的 DTS 元件。如果您想要從位於另一部電腦上的 SQL Server 執行個體匯入登入，您的 SQL Server 執行個體必須在網域帳戶下執行，才能完成這項工作。
  
  注意 DTS 方法會傳送密碼，而非原始的 SID。如果某個登入不是使用原始的 SID 建立的，而使用者資料庫也被傳送至新的伺服器上，那麼資料庫使用者將會從該登入被孤立出來。如果要傳送原始的 SID，並略過孤兒使用者，請依照＜在不同版本的 SQL Server 之間傳送登入和密碼的完整解決方案＞一節中的步驟執行。

回此頁最上方
如何在 SQL Server 2005 的執行個體之間傳送登入和密碼如需有關如何在 SQL Server 2005 的執行個體之間傳送登入和密碼的詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：918992  How to transfer the logins and the passwords between instances of SQL Server 2005
回此頁最上方
在不同版本的 SQL Server 之間傳送登入和密碼的完整解決方案這個方法適用於下列案例：

• 將登入和密碼從 SQL Server 7.0 傳送至 SQL Server 7.0。
• 將登入和密碼從 SQL Server 7.0 傳送至 SQL Server 2000。
• 將登入和密碼從 SQL Server 7.0 傳送至 SQL Server 2005。
• 在執行 SQL Server 2000 的伺服器之間傳送登入和密碼。
• 將登入和密碼從 SQL Server 2000 傳送至 SQL Server 2005。

注意 如需有關下列步驟的重要資訊，請檢視本文結尾部分的備註。

如果要在不同版本的 SQL Server 之間傳送登入和密碼，請依照下列步驟執行：

• 在來源 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_revlogin GOCREATE 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_curs FETCH 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 @tmpstr        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name           + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '      END      ELSE BEGIN         -- Null password        EXEC 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_curs DEALLOCATE login_curs RETURN 0GO ----- End Script -----
  
• 在您建立 sp_help_revlogin 預存程序之後，請透過 Query Analyzer 在來源伺服器上執行sp_help_revlogin 程序。sp_help_revlogin 預存程序可以在 SQL Server 7.0 和 SQL Server 2000 上使用。sp_help_revlogin 預存程序的輸出即為登入指令碼，這可用來建立具有原始 SID 和密碼的登入。請儲存輸出，再貼到目的 SQL Server 上的 Query Analyzer 中並加以執行。例如：EXEC master..sp_help_revlogin
  
  

回此頁最上方
備註

• 在目的 SQL Server 上執行輸出指令碼之前，請先仔細檢視指令碼的內容。如果您必須將登入傳送至 SQL Server 執行個體，且該 SQL Server 執行個體與來源 SQL Server 的執行個體位於不同的網域中，請編輯 sp_help_revlogin 程序所產生的指令碼，並以 sp_grantlogin 陳述式中的新網域取代網域名稱。因為新的網域中由整合登入所授予的權限將會與原始網域中的登入具有不同的 SID，所以資料庫使用者將會從這些登入中被孤立出來。如果要解決這些孤兒使用者的問題，請參閱下列項目符號所提到的參考文件。如果您在位於相同網域中的 SQL Server 執行個體之間傳送整合登入，由於仍會使用相同的 SID，因此，使用者可能不會被孤立。
• 在您移動這些登入之後，使用者可能沒有權限存取也被移動過的資料庫。這就是所謂的「孤兒使用者」問題。如果您嘗試授予登入權限給該資料庫，可能會失敗，並顯示該名使用者已經存在：Microsoft SQL-DMO (ODBC SQLState: 42000) 錯誤 15023: 使用者或角色 '%s' 在目前的資料庫中已經存在。
  
  
  如需有關如何將登入對應至資料庫使用者，以解決遭到孤立的 SQL Server 登入和整合登入的指示，請參閱「Microsoft 知識庫」中的下列文件：240872  INF：如何解決在 SQL Server 之間移動資料庫的權限問題
  如需有關如何使用 sp_change_users_login 預存程序逐一修正孤兒使用者問題的指示 (只能解決從標準 SQL 登入中被孤立出來的使用者)，請參閱「Microsoft 知識庫」中的下列文件：274188  PRB：線上叢書的疑難排解孤兒使用者主題不完整
• 如果傳送登入和密碼只是將資料庫移至執行 SQL Server 新伺服器上的一部分工作，請參閱「Microsoft 知識庫」中的下列文件，以取得相關工作流程和步驟的說明：314546  如何在執行 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 指令碼的輸出時，將會看到下列錯誤：伺服器: 訊息 15025，層級 16，狀態 1，程序 sp_addlogin，行 56
  登入 'test1' 已經存在。
  
  
  同樣地，如果您想要新增的登入與此伺服器上的某個現有登入具有相同的 SID 值，您會收到下列錯誤訊息：伺服器: 訊息 15433，層級 16，狀態 1，程序 sp_addlogin，行 93
  提供的參數 @sid 正在使用中。
  
  
  因此，您必須仔細地檢視這些命令的輸出、檢查 sysxlogins 資料表的內容，並逐一解決這些錯誤。
• 特定登入的 SID 值可用來做為在 SQL Server 中實作資料庫層級存取權限的基礎。因此，如果同一個登入在資料庫層級上有兩個不同的 SID 值 (位於該伺服器上兩個不同的資料庫中)，則該登入將只能存取其 SID 符合該登入在 syslogins 中 SID 值的資料庫。當您將這兩個討論中的資料庫從不同的伺服器整合在一起之後，可能會發生這種情況。如果要解決這個問題，您必須使用 sp_dropuser 預存程序，以手動方式將所討論的登入從 SID 不符的資料庫中移除，再使用 sp_adduser 預存程序重新加入該登入。

回此頁最上方

關鍵字：	kbsqldeveloper kbhowtomaster kbinfo KB246133

回此頁最上方
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應，不負任何擔保責任。Microsoft及(或)其供應商謹此聲明，不負任何對與此資訊有關之擔保責任，包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。