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

適用於: Microsoft SQL Server 2005 Standard EditionMicrosoft SQL Server 2005 Workgroup EditionMicrosoft SQL Server 2005 Developer Edition

简介


本文將說明,如何在 Microsoft SQL Server 不同的執行個體間傳送登入和密碼。

注意執行個體可能位於同一部伺服器或不同伺服器上,而且版本可能不同。

如需有關如何在其他版本 SQL Server 執行個體間傳送登入和密碼的詳細資訊,請按一下以下的文件編號,檢視 Microsoft 知識庫中的文件:

246133 如何在執行舊版 SQL Server 的 SQL Server 執行個體間傳輸登入和密碼

其他相關資訊


在本文中,伺服器 A 和伺服器 B 是不同的伺服器。 
 
將資料庫從伺服器 A 上的 SQL Server 執行個體移到伺服器 B 上的 SQL Server 執行個體後,使用者可能無法登入伺服器 B 上的資料庫。此外,使用者可能會收到下列錯誤訊息:
使用者「MyUserr」登入失敗。 (Microsoft SQL Server,錯誤: 18456)
之所以發生這個問題,是因為您未將登入和密碼從伺服器 A 上的 SQL Server 執行個體傳送到伺服器 B 上的 SQL Server 執行個體。

若要傳輸登入,請根據您的情況,使用下列其中一個方法。

方法 1: 重設目的地 SQL Server 電腦(伺服器 B)上的密碼

若要解決此問題,請重設 SQL Server 電腦中的密碼,然後再編寫登入。

注意您重設密碼時使用密碼雜湊演算法。

方法 2: 使用來源伺服器(伺服器 B)上產生的指令碼,將登入和密碼傳輸到目的地伺服器(伺服器 A)

若要在有空白密碼的指令碼建立記錄檔,請依照下列步驟執行:
  1. 在伺服器 A 上,啟動 SQL Server Management Studio,然後連線至先前從該處移動資料庫的 SQL Server 的執行個體。
  2. 開啟新的 [查詢編輯器] 視窗,然後執行下列指令碼。
    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 (514) OUTPUT
    AS
    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
    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 @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 @defaultdb sysname

    IF (@login_name IS NULL)
    DECLARE login_curs CURSOR FOR

    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin 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'
    ELSE
    DECLARE login_curs CURSOR FOR


    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin 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
    OPEN login_curs

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
    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
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    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 + ']'

    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
    PRINT @tmpstr
    END

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
    END
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
    GO


    注意這個指令碼會在 master 資料庫建立兩個程序。 這些程序稱為  sp_hexadecimalsp_help_revlogin
  3. 在相同或新的查詢視窗中執行下列陳述式: 
    EXEC sp_help_revlogin
    sp_help_revlogin 程序所產生的輸出指令碼是登入指令碼。 這個登入指令碼會建立具有原始「安全性識別碼」(SID) 和原始密碼的登入。

 目的地伺服器(伺服器 B)的步驟:

  1. 在伺服器 B 上,啟動 SQL Server Management Studio,然後連線至先前將資料庫移至該處的 SQL Server 的執行個體。

    重要進行步驟 2 前,請先檢閱下文「備註」一節的資訊。
  2. 開啟新的 [查詢編輯器] 視窗,然後執行在前述程序中步驟 2 產生的輸出指令碼。

備註

在伺服器 B 上的執行個體上執行輸出指令碼之前,請先檢閱下列資訊:

  • 雜湊密碼的可行方式如下:
    • VERSION_SHA1: 此雜湊是使用 SHA1 演算法產生,用於 SQL Server 2000 的 SQL Server 2008 R2。
    • VERSION_SHA2: 此雜湊是使用 SHA2 512 演算法產生,用於 SQL Server 2012 及更新版本。
  • 仔細檢閱輸出指令碼。 如果伺服器 A 和伺服器 B 在不同的網域,您必須修改輸出指令碼。 然後,您必須在 CREATE LOGIN 的陳述式中,使用新的網域名稱取代原始的網域名稱。 在新網域中授與存取權的整合式登入沒有與原始網域中的登入相同的 SID。 因此,使用者是孤立於這些登入之外。 如需有關如何解決這些孤立使用者的詳細資訊,請按一下下面的文件編號,檢視「Microsoft 知識庫」中的文件:

    240872 如何解決在執行 SQL Server 的伺服器間移動資料庫的權限問題

    如果伺服器 A 和伺服器 B 是在相同的網域中,就會使用相同的 SID。 因此,使用者不可能孤立。
  • 在輸出指令碼中,登入是使用加密的密碼而建立的。 這是因為 CREATE LOGIN 陳述式中的 HASHED 引數的緣故。 這個引數會指定在 PASSWORD 引數之後輸入的密碼已經是雜湊密碼。
  • 根據預設,只有 sysadmin 固定伺服器角色的成員可以從 sys.server_principals 檢視執行 SELECT 陳述式。 除非 sysadmin 固定伺服器角色的成員授與必要權限給使用者,否則使用者無法建立或執行輸出指令碼。
  • 本文中的步驟無法為特定登入傳送預設資料庫資訊。 這是因為預設資料庫不一定永遠在伺服器 B 上。如果要定義登入的預設資料庫,請傳入登入名稱和預設資料庫做為引數,藉此使用 ALTER LOGIN 陳述式。
  • 排序來源和目的地伺服器的順序:
    • 不區分大小寫的伺服器 A 和區分大小寫的伺服器 B: 伺服器 A 的排序順序可能不須區分大小寫,伺服器 B 的排序順序則可能須區分大小寫。 在此情況下,在您將登入和密碼傳送到伺服器 B 上的執行個體後,使用者必須以大寫字母輸入密碼的所有字母。
    • 區分大小寫的伺服器 A 和不區分大小寫的伺服器 B: 伺服器 A 的排序順序可能須區分大小寫,伺服器 B 的排序順序則可能不須區分大小寫。 在此情況下,使用者無法使用您傳送到伺服器 B 上的執行個體的登入和密碼登入,除非下列其中一種情況成立:
      • 原始密碼中沒有字母。
      • 原始密碼中的所有字母都是大寫字母。
    • 這兩部伺服器區分大小寫或不區分大小寫: 伺服器 A 和伺服器 B 的排序順序可能要區分大小寫,也可能不區分大小寫。 在上述情況中,使用者不會遇到問題。
  • 已存在於伺服器 B 上執行個體中的登入可能會與輸出指令碼的名稱相同。 在這種情況中,當您在伺服器 B 上的執行個體上執行輸出指令碼時,會收到下列錯誤訊息:
    訊息 15025,層級 16,狀態 1,行 1
    伺服器主體「MyLogin」已經存在。
    同樣地,已存在於伺服器 B 上執行個體中的登入 SID 可能會與輸出指令碼中的 SID 相同。 在這種情況中,當您在伺服器 B 上的執行個體上執行輸出指令碼時,會收到下列錯誤訊息:
    訊息 15433,層級 16,狀態 1,行 1
    提供的參數 sid 正在使用中。
    因此,您必須執行下列操作:
    1. 仔細檢閱輸出指令碼。
    2. 檢查伺服器 B 上執行個體中的 sys.server_principals 檢視的內容。
    3. 視情況解決這些錯誤訊息。

      在 SQL Server 2005 中,登入的 SID 會用來實作資料庫層級的存取。 在伺服器上的不同資料庫中,登入可能會有不同的 SID。 在此情況下,登入只能存取其 SID 與 sys.server_principals 檢視中的 SID 相符的資料庫。 如果兩個資料庫是從不同的伺服器合併,可能就會發生這個問題。 如果要解決這個問題,請使用 DROP USER 陳述式從其 SID 不符的資料庫手動移除登入。 然後,使用 CREATE USER 陳述式再次加入登入。
  • 如果您嘗試使用已編寫指令碼的 pre-SQL Server 2000 登入建立新的 SQL Server 2012 登入,您會收到下列錯誤訊息:

    Msg 15021, Level 16, State 2, Line 1 (訊息 7311,層級 16,狀態 2,行 1)
    參數 PASSWORD 指定的值無效。 指定有效的參數值。

    注意 由於 16 位元密碼雜湊提供給 CREATE LOGIN 和 ALTER LOGIN 陳述式,因此您會在 SQL Server 2012 中收到此錯誤。

    若要在執行 SQL Server 2012 的伺服器上解決此問題,請建立具有空白密碼的登入。 如果要執行這項操作,請執行以下指令碼:
    CREATE LOGIN [Test] WITH PASSWORD = '', SID = 0x90FD605DCEFAE14FAB4D5EB0BBA1AECC, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
    在您建立具有空白密碼的登入後,使用者就可以在下次登入時變更密碼。

方法 3: 使用 pre-SQL Server 2000 密碼登入

注意只有您將 SQL Server 2000 移轉至較新版本的 SQL Server 時,才可使用此方法。

在此情況下,請要求使用者使用 pre-SQL Server 2000 登入,登入執行 SQL Server 的伺服器。

注意使用者使用 pre-SQL Server 2000 密碼登入時,密碼雜湊會自動更新。

參考


如需有關如何疑難排解孤立使用者的詳細資訊,請前往下列疑難排解孤立使用者 Microsoft Developer Network (MSDN) 網站:

如需 CREATE LOGIN 陳述式的詳細資訊,請前往 CREATE LOGIN (Transact-SQL) MSDN 網站。

如需有關 ALTER LOGIN 陳述式的詳細資訊,請前往 ALTER LOGIN (Transact-SQL) MSDN 網站。