在 SQL Server 執行個體之間傳輸登入和密碼
本文說明如何在 Windows 上執行的不同 SQL Server 執行個體之間傳送登入和密碼。
原始產品版本: SQL S
原始 KB 編號: 918992、246133
簡介
本文說明如何在 Microsoft SQL Server 的不同執行個體之間傳輸登入和密碼。
注意事項
這些實例可能位於相同伺服器或不同伺服器上,而且其版本可能不同。
其他資訊
在本文中,伺服器 A 和伺服器 B 是不同的伺服器。
將資料庫從伺服器 A 上的 SQL Server 實體移至伺服器 B 上的 SQL Server 實體之後,使用者可能無法登入伺服器 B 上的資料庫。此外,使用者可能會收到下列錯誤訊息:
使用者 'MyUser' 登入失敗。 (Microsoft SQL Server,錯誤:18456)
發生此問題的原因是您未將登入和密碼從伺服器 A 上的 SQL Server 實體傳輸到伺服器 B 上的 SQL Server 實體。
注意事項
18456 錯誤訊息也會因為其他原因而發生。 如需這些原因和可能解決方式的其他資訊,請 參閱MSSQLSERVER_18456。
若要傳輸登入,請根據您的情況,使用下列其中一種方法。
方法 1:將目的地 SQL Server 電腦上的密碼重設 (伺服器 B) 。
若要解決此問題,請重設 SQL Server 電腦中的密碼,然後編寫登入的指令碼。
注意事項
當您重設密碼時,會使用密碼雜湊演算法。
方法 2:使用來源伺服器 (伺服器 A) 上產生的腳本,將登入和密碼傳送至目的地伺服器 (Server B) 。
建立預存程序,以協助產生必要的指令碼來傳輸登入及其密碼。 為此,請使用 SQL Server Management Studio (SSMS) 或任何其他用戶端工具連線到伺服器 A,然後執行下列指令碼:
USE [master] GO IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE [dbo].[sp_hexadecimal] ( @binvalue varbinary(256), @hexvalue varchar (514) OUTPUT ) AS BEGIN DECLARE @charvalue varchar (514) 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 END go IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE [dbo].[sp_help_revlogin] ( @login_name sysname = NULL ) AS BEGIN DECLARE @name SYSNAME DECLARE @type VARCHAR (1) DECLARE @hasaccess INT DECLARE @denylogin INT DECLARE @is_disabled INT DECLARE @PWD_varbinary VARBINARY (256) DECLARE @PWD_string VARCHAR (514) DECLARE @SID_varbinary VARBINARY (85) DECLARE @SID_string VARCHAR (514) DECLARE @tmpstr VARCHAR (1024) DECLARE @is_policy_checked VARCHAR (3) DECLARE @is_expiration_checked VARCHAR (3) Declare @Prefix VARCHAR(255) DECLARE @defaultdb SYSNAME DECLARE @defaultlanguage SYSNAME DECLARE @tmpstrRole VARCHAR (1024) IF (@login_name IS NULL) BEGIN DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa' AND p.name not like '##%' ORDER BY p.name END ELSE DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name ORDER BY p.name OPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage 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 '' WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN PRINT '' SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr SET @tmpstr='IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''+@name+''') BEGIN' Print @tmpstr IF (@type IN ( 'G', 'U')) BEGIN -- NT authenticated account/group SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']' END ELSE BEGIN -- SQL Server authentication -- obtain password and sid SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) ) EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT -- obtain password policy state SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']' IF ( @is_policy_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked END IF ( @is_expiration_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked END END IF (@denylogin = 1) BEGIN -- login is denied access SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name ) END ELSE IF (@hasaccess = 0) BEGIN -- login exists but does not have access SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name ) END IF (@is_disabled = 1) BEGIN -- login is disabled SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE' END SET @Prefix = ' EXEC master.dbo.sp_addsrvrolemember @loginame=''' SET @tmpstrRole='' SELECT @tmpstrRole = @tmpstrRole + CASE WHEN sysadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''sysadmin''' ELSE '' END + CASE WHEN securityadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''securityadmin''' ELSE '' END + CASE WHEN serveradmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''serveradmin''' ELSE '' END + CASE WHEN setupadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''setupadmin''' ELSE '' END + CASE WHEN processadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''processadmin''' ELSE '' END + CASE WHEN diskadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''diskadmin''' ELSE '' END + CASE WHEN dbcreator = 1 THEN @Prefix + [LoginName] + ''', @rolename=''dbcreator''' ELSE '' END + CASE WHEN bulkadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''bulkadmin''' ELSE '' END FROM ( SELECT CONVERT(VARCHAR(100),SUSER_SNAME(sid)) AS [LoginName], sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator, bulkadmin FROM sys.syslogins WHERE ( sysadmin<>0 OR securityadmin<>0 OR serveradmin<>0 OR setupadmin <>0 OR processadmin <>0 OR diskadmin<>0 OR dbcreator<>0 OR bulkadmin<>0 ) AND name=@name ) L PRINT @tmpstr PRINT @tmpstrRole PRINT 'END' END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage END CLOSE login_curs DEALLOCATE login_curs RETURN 0 END
注意事項
此指令碼會在 master 資料庫中建立兩個預存程序。 這些程式會命名 為 sp_hexadecimal 和 sp_help_revlogin。
在相同或新的查詢視窗中執行下列語句:
EXEC sp_help_revlogin
預存程序產生的輸出指令碼
sp_help_revlogin
是登入指令碼。 此登入指令碼會建立具有原始安全識別碼 (SID) 和原始密碼的登入。
目的地伺服器上的步驟 (伺服器 B)
使用 SSMS) 等任何用戶端工具 (連線到伺服器 B,然後執行步驟 4 中產生的腳本, (sp_helprevlogin
從伺服器 A 輸出) 。
註解
在伺服器 B 上的執行個體上執行輸出指令碼之前,請先檢閱下列資訊:
您可以透過下列方式雜湊密碼:
VERSION_SHA1
:此雜湊是使用 SHA1 演算法所產生,並從 SQL Server 2000 使用至 SQL Server 2008 R2。VERSION_SHA2
:此雜湊是使用 SHA2 512 演算法所產生,並用於 SQL Server 2012 和更新版本。
請仔細檢閱輸出指令碼。 如果伺服器 A 和伺服器 B 位於不同的網域,您必須變更輸出指令碼。 然後,您必須在語句中
CREATE LOGIN
使用新的功能變數名稱來取代原始功能變數名稱。 新網域中授與存取權的整合式登入與原始網域中的登入沒有相同的 SID。 因此,使用者會從這些登入中孤立。 如需如何解決這些孤立使用者的詳細資訊,請參閱針對孤立的使用者 (SQL Server) 和 ALTER USER進行疑難解答。
如果伺服器 A 和伺服器 B 位於相同的網域中,則會使用相同的 SID。 因此,使用者不太可能遭到孤立。在輸出指令碼中,會使用加密的密碼來建立登入。 這是由於
CREATE LOGIN
語句中的 HASHED 引數導致。 這個引數會指定已雜湊 PASSWORD 引數之後輸入的密碼。預設情況下,只有系統管理員固定伺服器角色的成員可以從
sys.server_principals
檢視執行SELECT
語句。 除非系統管理員固定伺服器角色的成員將必要的許可權授與使用者,否則使用者無法建立或執行輸出腳本。本文中的步驟不會傳送特定登入的預設資料庫資訊。 這是因為預設資料庫不一定會存在於伺服器 B 上。若要定義登入的預設資料庫,請藉由傳入登入名稱和預設資料庫作為自變數來使用
ALTER LOGIN
語句。排序來源和目的地伺服器上的訂單:
不區分大小寫的伺服器 A 和區分大小寫的伺服器 B:伺服器 A 的排序順序可能不區分大小寫,而且伺服器 B 的排序順序可能區分大小寫。 在此情況下,使用者必須在您將登入和密碼傳輸至伺服器 B 上的執行個體之後,均以大寫字母輸入密碼。
區分大小寫的伺服器 A 和不區分大小寫的伺服器 B: 伺服器 A 的排序順序可能區分大小寫,而且伺服器 B 的排序順序可能不區分大小寫。 在此情況下,除非下列其中一個條件成立,否則使用者無法使用您在伺服器 B 上傳送至實例的登入和密碼來登入:
- 原始密碼不包含字母。
- 原始密碼中的所有字母都是大寫字母。
兩部伺服器上的區分大小寫或不區分大小寫:伺服器 A 和伺服器 B 的排序順序可能區分大小寫,或伺服器 A 和伺服器 B 的排序順序可能不區分大小寫。 在這些情況下,使用者不會遇到問題。
已經在伺服器 B 實例中的登入,其名稱可能與輸出腳本中的名稱相同。 在此情況下,當您在伺服器 B 上的執行個體上執行輸出指令碼時,會收到下列錯誤訊息:
Msg 15025, Level 16, State 1, Line 1
伺服器主體 'MyLogin' 已經存在。同樣地,已經在伺服器 B 實例中的登入,可能會有與輸出腳本中的 SID 相同的 SID。 在此情況下,當您在伺服器 B 上的執行個體上執行輸出指令碼時,會收到下列錯誤訊息:
Msg 15433, Level 16, State 1, Line 1 Supplied parameter sid is in use.
因此,您必須執行下列動作:
請仔細檢閱輸出指令碼。
檢查伺服器 B 上
sys.server_principals
實例中的檢視內容。適當地處理這些錯誤訊息。
在 SQL Server 2005 中,登入的 SID 是用來實作資料庫層級存取。 在伺服器的不同資料庫中,登入可能會有不同的 SID。 在此情況下,登入只能存取具有符合
sys.server_principals
檢視中 SID 之 SID 的資料庫。 如果兩個資料庫是從不同的伺服器合併而來,就可能發生此問題。 若要解決此問題,請使用 DROP USER 語句,從 SID 不符的資料庫中手動移除登入。 然後,使用CREATE USER
語句再次新增登入。
參考
意見反應
https://aka.ms/ContentUserFeedback。
即將登場:在 2024 年,我們將逐步淘汰 GitHub 問題作為內容的意見反應機制,並將它取代為新的意見反應系統。 如需詳細資訊,請參閱:提交並檢視相關的意見反應