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

發行項
09/06/2024

本文說明如何在 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('dbo.sp_hexadecimal') IS NOT NULL
    DROP PROCEDURE dbo.sp_hexadecimal
GO
CREATE PROCEDURE dbo.sp_hexadecimal
     @binvalue [varbinary](256)
    ,@hexvalue [nvarchar] (514) OUTPUT
AS
BEGIN
    DECLARE @i [smallint]
    DECLARE @length [smallint]
    DECLARE @hexstring [nchar](16)
    SELECT @hexvalue = N'0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH(@binvalue)
    SELECT @hexstring = N'0123456789ABCDEF'
    WHILE (@i < =  @length)
    BEGIN
        DECLARE @tempint   [smallint]
        DECLARE @firstint  [smallint]
        DECLARE @secondint [smallint]
        SELECT @tempint = CONVERT([smallint], SUBSTRING(@binvalue, @i, 1))
        SELECT @firstint = FLOOR(@tempint / 16)
        SELECT @secondint = @tempint - (@firstint * 16)
        SELECT @hexvalue = @hexvalue
            + SUBSTRING(@hexstring, @firstint  + 1, 1)
            + SUBSTRING(@hexstring, @secondint + 1, 1)
        SELECT @i = @i + 1
    END
END
GO
IF OBJECT_ID('dbo.sp_help_revlogin') IS NOT NULL
    DROP PROCEDURE dbo.sp_help_revlogin
GO
CREATE PROCEDURE dbo.sp_help_revlogin
    @login_name [sysname] = NULL
AS
BEGIN
    DECLARE @name                  [sysname]
    DECLARE @type                  [nvarchar](1)
    DECLARE @hasaccess             [int]
    DECLARE @denylogin             [int]
    DECLARE @is_disabled           [int]
    DECLARE @PWD_varbinary         [varbinary](256)
    DECLARE @PWD_string            [nvarchar](514)
    DECLARE @SID_varbinary         [varbinary](85)
    DECLARE @SID_string            [nvarchar](514)
    DECLARE @tmpstr                [nvarchar](4000)
    DECLARE @is_policy_checked     [nvarchar](3)
    DECLARE @is_expiration_checked [nvarchar](3)
    DECLARE @Prefix                [nvarchar](4000)
    DECLARE @defaultdb             [sysname]
    DECLARE @defaultlanguage       [sysname]
    DECLARE @tmpstrRole            [nvarchar](4000)
    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,default_language_name = ISNULL(p.default_language_name,@@LANGUAGE)
        FROM sys.server_principals p
        LEFT JOIN sys.syslogins l ON l.[name] = p.[name]
        WHERE p.[type] IN ('S' /* SQL_LOGIN */,'G' /* WINDOWS_GROUP */,'U' /* WINDOWS_LOGIN */)
            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,default_language_name = ISNULL(p.default_language_name,@@LANGUAGE)
        FROM sys.server_principals p
        LEFT JOIN sys.syslogins l ON l.[name] = p.[name]
        WHERE p.[type] IN ('S' /* SQL_LOGIN */,'G' /* WINDOWS_GROUP */,'U' /* WINDOWS_LOGIN */)
            AND p.[name] <> 'sa'
            AND p.[name] NOT LIKE '##%'
            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 for ' + QUOTENAME(@login_name) + N'. */'
        CLOSE login_curs
        DEALLOCATE login_curs
        RETURN - 1
    END
    SET @tmpstr = N'/* sp_help_revlogin script
** Generated ' + CONVERT([nvarchar], GETDATE()) + N' on ' + @@SERVERNAME + N'
*/'
    PRINT @tmpstr
    WHILE (@@fetch_status <> - 1)
    BEGIN
        IF (@@fetch_status <> - 2)
        BEGIN
            PRINT ''
            SET @tmpstr = N'/* Login ' + QUOTENAME(@name) + N' */'
            PRINT @tmpstr
            SET @tmpstr = N'IF NOT EXISTS (
    SELECT 1
    FROM sys.server_principals
    WHERE [name] = N''' + @name + N'''
    )
BEGIN'
            PRINT @tmpstr
            IF @type IN ('G','U') -- NT-authenticated Group/User
            BEGIN -- NT authenticated account/group 
                SET @tmpstr = N'    CREATE LOGIN ' + QUOTENAME(@name) + N'
    FROM WINDOWS
    WITH DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + N'
        ,DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)
            END
            ELSE
            BEGIN -- SQL Server authentication
                -- obtain password and sid
                SET @PWD_varbinary = CAST(LOGINPROPERTY(@name, 'PasswordHash') AS [varbinary](256))
                EXEC dbo.sp_hexadecimal @PWD_varbinary, @PWD_string OUT
                EXEC dbo.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 = NCHAR(9) + N'CREATE LOGIN ' + QUOTENAME(@name) + N'
    WITH PASSWORD = ' + @PWD_string + N' HASHED
        ,SID = ' + @SID_string + N'
        ,DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + N'
        ,DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)

                IF @is_policy_checked IS NOT NULL
                BEGIN
                    SET @tmpstr = @tmpstr + N'
        ,CHECK_POLICY = ' + @is_policy_checked
                END

                IF @is_expiration_checked IS NOT NULL
                BEGIN
                    SET @tmpstr = @tmpstr + N'
        ,CHECK_EXPIRATION = ' + @is_expiration_checked
                END
            END
            IF (@denylogin = 1)
            BEGIN -- login is denied access
                SET @tmpstr = @tmpstr
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'DENY CONNECT SQL TO ' + QUOTENAME(@name)
            END
            ELSE IF (@hasaccess = 0)
            BEGIN -- login exists but does not have access
                SET @tmpstr = @tmpstr
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'REVOKE CONNECT SQL TO ' + QUOTENAME(@name)
            END
            IF (@is_disabled = 1)
            BEGIN -- login is disabled
                SET @tmpstr = @tmpstr
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'ALTER LOGIN ' + QUOTENAME(@name) + N' DISABLE'
            END
            SET @Prefix =
                  NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'EXEC [master].dbo.sp_addsrvrolemember @loginame = N'''
            SET @tmpstrRole = N''
            SELECT @tmpstrRole = @tmpstrRole
                + CASE WHEN sysadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''sysadmin''' ELSE '' END
                + CASE WHEN securityadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''securityadmin''' ELSE '' END
                + CASE WHEN serveradmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''serveradmin''' ELSE '' END
                + CASE WHEN setupadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''setupadmin''' ELSE '' END
                + CASE WHEN processadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''processadmin''' ELSE '' END
                + CASE WHEN diskadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''diskadmin''' ELSE '' END
                + CASE WHEN dbcreator = 1 THEN @Prefix + LoginName + N''', @rolename = N''dbcreator''' ELSE '' END
                + CASE WHEN bulkadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''bulkadmin''' ELSE '' END
            FROM (
                SELECT
                     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
            IF @tmpstr <> '' PRINT @tmpstr
            IF @tmpstrRole <> '' 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。

在 SSMS 查詢編輯器中，選取 [結果到文字] 選項。
在相同或新的查詢視窗中執行下列語句：
```
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.

因此，您必須執行下列動作：
1. 請仔細檢閱輸出指令碼。
2. 檢查伺服器 B 上 sys.server_principals 實例中的檢視內容。
3. 適當地處理這些錯誤訊息。
  
  在 SQL Server 2005 中，登入的 SID 是用來實作資料庫層級存取。在伺服器的不同資料庫中，登入可能會有不同的 SID。在此情況下，登入只能存取具有符合 sys.server_principals 檢視中 SID 之 SID 的資料庫。如果兩個資料庫是從不同的伺服器合併而來，就可能發生此問題。若要解決此問題，請使用 DROP USER 語句，從 SID 不符的資料庫中手動移除登入。然後，使用 CREATE USER 語句再次新增登入。

共用方式為

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

簡介

其他資訊

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

註解

參考

意見反應

其他資源