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

發行項
01/17/2025

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

原始產品版本：SQL Server
原始 KB 編號： 918992、246133

簡介

本文說明如何在 Microsoft SQL Server 的不同執行個體之間傳輸登入和密碼。實例可能位於相同伺服器或不同的伺服器上，而且其版本可能會不同。

為什麼要在 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：透過來源伺服器上的 SSMS 產生腳本，並在目的地伺服器上手動重設 SQL Server 登入的密碼

您可以使用資料庫的 [產生腳本] 選項，在 SQL Server Management Studio （SSMS）中產生登入腳本。

若要透過來源伺服器上的 SSMS 產生腳本，並手動重設目的地伺服器上 SQL Server 登入的密碼，請遵循下列步驟：

連接到裝載來源 SQL Server 的伺服器 A。
展開 [資料庫] 節點。
以滑鼠右鍵按下任何使用者資料庫，然後選取 [>工作產生腳本]。
[簡介] 頁面隨即開啟。選取 [下一步 ] 以開啟 [ 選擇物件] 頁面。選取 [編寫整個資料庫和所有資料庫物件的指令碼] 。
選取 [下一步] 來開啟 [設定指令碼編寫選項] 頁面。
選取 [腳稿登入] 選項的 [ 進階 ] 按鈕。
在 [進階] 清單中，尋找 [腳本登入]，將選項設定為 True，然後選取 [確定]。
回到 [選取腳本儲存方式] 底下的 [設定腳本選項]，然後選取 [在新查詢視窗中開啟]。
選取 [下一步 ] 兩次，然後選取 [ 完成]。
在包含登入的腳本中尋找區段。一般而言，產生的腳本會在本節開頭包含具有下列批註的文字：

/* For security reasons the login is created disabled and with a random password. */

注意

這表示 SQL Server 驗證登入是以隨機密碼產生，且預設為停用。您必須重設密碼，並在目的地伺服器上重新啟用這些登入。
將登入腳本從較大的產生的腳本套用至目的地 SQL Server。
針對任何 SQL Server 驗證登入，重設目的地 SQL Server 上的密碼，然後重新啟用這些登入。

方法 2：使用來源伺服器上產生的文稿，將登入和密碼傳輸至目的地伺服器（伺服器 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）。
執行產生為伺服器 A 輸出的 sp_helprevlogin 文稿。

備註

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

密碼哈希資訊

您可以透過下列方式雜湊密碼：
- VERSION_SHA1：此雜湊是使用 SHA1 演算法所產生，並從 SQL Server 2000 使用至 SQL Server 2008 R2。
- VERSION_SHA2：此雜湊是使用 SHA2 512 演算法所產生，並用於 SQL Server 2012 和更新版本。
在輸出指令碼中，會使用加密的密碼來建立登入。這是因為 HASHED 語句中的 CREATE LOGIN 自變數。這個自變數會指定在自變數哈希之後 PASSWORD 輸入的密碼。

如何處理網域變更

您的來源和目的地伺服器位於不同的網域嗎？請仔細檢閱輸出指令碼。如果伺服器 A 和伺服器 B 位於不同的網域，您必須變更輸出指令碼。然後，您必須使用語句中的 CREATE LOGIN 新功能變數名稱來取代原始功能變數名稱。在新網域中授與存取權的整合式登入，與原始網域中的登入沒有相同的 SID。因此，使用者會從這些登入中孤立。如需如何解決這些孤立使用者的詳細資訊，請參閱針對孤立使用者（SQL Server）和 ALTER USER 進行疑難解答。

如果伺服器 A 和伺服器 B 位於相同的網域中，則會使用相同的 SID。因此，使用者不太可能遭到孤立。

檢視和選取所有登入的許可權

預設情況下，只有系統管理員固定伺服器角色的成員可以從 sys.server_principals 檢視執行 SELECT 語句。除非系統管理員固定伺服器角色的成員授與使用者所需的許可權，否則使用者無法建立或執行輸出腳本。

預設資料庫設定不會編寫文本並傳輸

本文中的步驟不會傳送特定登入的預設資料庫資訊。這是因為預設資料庫可能不一定存在於伺服器 B 上。若要定義登入的預設資料庫，請使用 ALTER LOGIN 語句，方法是傳入登入名稱和預設資料庫作為自變數。

如何處理來源與目的地伺服器之間的不同排序順序

來源和目的地伺服器之間的排序順序可能會有差異，或者它們可能相同。以下是每個案例的解決方式：

不區分大小寫的伺服器 A 和區分大小寫的伺服器 B：伺服器 A 的排序順序可能不區分大小寫，而伺服器 B 的排序順序可能會區分大小寫。在此情況下，使用者必須在您將登入和密碼傳輸至伺服器 B 上的執行個體之後，均以大寫字母輸入密碼。
區分大小寫的伺服器 A 和不區分大小寫的伺服器 B： 伺服器 A 的排序順序可能會區分大小寫，而伺服器 B 的排序順序可能不區分大小寫。在此情況下，除非下列其中一個條件成立，否則使用者無法使用您傳送至伺服器 B 實例的登入和密碼來登入：
- 原始密碼不包含字母。
- 原始密碼中的所有字母都是大寫字母。
這兩部伺服器上的區分大小寫或區分大小寫：伺服器 A 和伺服器 B 的排序順序可能會區分大小寫，或者伺服器 A 和伺服器 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。如果資料庫是從不同的伺服器手動合併，就可能發生此問題。在這種情況下，登入只能存取資料庫主體的 SID 符合檢視中 sys.server_principals SID 的資料庫。若要解決此問題，請使用DROP USER語句，手動移除資料庫使用者與不相符的SID。然後，使用 CREATE USER 語句再次新增使用者，並將它對應至正確的登入（伺服器主體）。

如需詳細資訊和區分伺服器與資料庫主體，請參閱 CREATE USER 和 CREATE LOGIN。

共用方式為

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

簡介

為什麼要在 SQL Server 實例之間傳輸登入？

傳輸登入的步驟

方法 1：透過來源伺服器上的 SSMS 產生腳本，並在目的地伺服器上手動重設 SQL Server 登入的密碼

方法 2：使用來源伺服器上產生的文稿，將登入和密碼傳輸至目的地伺服器（伺服器 B）

備註

密碼哈希資訊

如何處理網域變更

檢視和選取所有登入的許可權

預設資料庫設定不會編寫文本並傳輸

如何處理來源與目的地伺服器之間的不同排序順序

如何處理目的地伺服器上已經存在的登入

參考資料

意見反應

其他資源

共用方式為

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

簡介

為什麼要在 SQL Server 實例之間傳輸登入？

傳輸登入的步驟

方法 1：透過來源伺服器上的 SSMS 產生腳本，並在目的地伺服器上手動重設 SQL Server 登入的密碼

方法 2：使用來源伺服器上產生的文稿，將登入和密碼傳輸至目的地伺服器 （伺服器 B）

備註

密碼哈希資訊

如何處理網域變更

檢視和選取所有登入的許可權

預設資料庫設定不會編寫文本並傳輸

如何處理來源與目的地伺服器之間的不同排序順序

如何處理目的地伺服器上已經存在的登入

參考資料

意見反應

其他資源

方法 2：使用來源伺服器上產生的文稿，將登入和密碼傳輸至目的地伺服器（伺服器 B）