在不同版本的 SQL Server 之間傳輸登入和密碼的完整解決方案

在不同版本的 SQL Server 之間傳輸登入和密碼的完整解決方案
在不同版本的 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 之間傳輸登入和密碼，請按下列步驟操作：1. 在源 SQL Server 上運行以下指令碼。此指令碼可在 master 資料庫中建立名為 sp_hexadecimal 和 sp_help_revlogin 的兩個預存程序。請在完成過程的建立之後繼續執行第 2 步。

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

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
Drop PROCEDURE sp_hexadecimal
GO
Create PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
Select @charvalue = '0x'
Select @i = 1
Select @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 + 1
END
Select @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
Drop PROCEDURE sp_help_revlogin
GO
Create PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @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_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
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
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- 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.

? 能夠這樣做的原因在於：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 值與您要嘗試添加的登入的 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 預存程序添加它。