在 SQL Server 实例之间传输登录名和密码

项目
12/14/2024

本文介绍如何在 Windows 上运行的 SQL Server 的不同实例之间传输登录名和密码。

原始产品版本：SQL Server
原始 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）上生成的脚本将登录名和密码传输到目标服务器（服务器 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，然后运行服务器 A 的步骤 4（输出 sp_helprevlogin）中生成的脚本。

注解

在对服务器 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 参数后输入的密码已经过哈希处理。
默认情况下，只有“sysadmin”固定服务器角色的成员可以从 sys.server_principals 视图运行 SELECT 语句。除非 sysadmin 固定服务器角色的成员向用户授予必要的权限，否则用户无法创建或运行输出脚本。
本文中的步骤不会传输特定登录名的默认数据库信息。这是因为默认数据库可能始终存在于服务器 B 上。若要为登录名定义默认数据库，请使用 ALTER LOGIN 该语句，方法是传入登录名和默认数据库作为参数。
对源服务器和目标服务器进行排序：
- 不区分大小写的服务器 A 和区分大小写的服务器 B：服务器 A 的排序顺序可能不区分大小写，服务器 B 的排序顺序可能区分大小写。在此情况下，在将登录名和密码传输到服务器 B 上的实例之后，必须以全部大写字母的形式来键入密码。
- 区分大小写的服务器 A 和不区分大小写的服务器 B： 服务器 A 的排序顺序可能区分大小写，服务器 B 的排序顺序可能不区分大小写。在这种情况下，用户无法使用登录名和传输到服务器 B 上的实例的密码登录，除非满足以下条件之一：
  - 原始密码不包含字母。
  - 原始密码中的所有字母都是大写字母。
- 两台服务器上的区分大小写或区分大小写：服务器 A 和服务器 B 的排序顺序可能区分大小写，或者服务器 A 和服务器 B 的排序顺序可能不区分大小写。在这些情况下，用户不会遇到问题。
服务器 B 上实例中已有的登录名可能与输出脚本中的名称相同。在此情况下，在对服务器 B 上的实例运行输出脚本时，会接收到下面的错误消息：

消息 15025, 级别 16, 状态 1, 行 1
服务器主体“MyLogin”已存在。

同样，服务器 B 上的实例中已有的登录名可能具有与输出脚本中的 SID 相同的 SID。在此情况下，在对服务器 B 上的实例运行输出脚本时，会接收到下面的错误消息：

Msg 15433，级别 16，状态 1，第 1 行提供的参数 sid 正在使用中。

因此，必须执行以下操作：
1. 仔细检查输出脚本。
2. 检查服务器 B 上实例中视图的内容 sys.server_principals 。
3. 根据需要解决这些错误消息相关问题。
  
  在 SQL Server 2005 中，登录名的 SID 用于实现数据库级别的访问。登录名在服务器上的不同数据库中可能有不同的 SID。在此情况下，该登录名只能访问具有与 sys.server_principals 视图中的 SID 匹配的 SID 的数据库。如果两个数据库从不同的服务器组合，则可能会出现此问题。若要解决此问题，可使用 DROP USERT 语句，从具有不匹配的 SID 的数据库中手动删除相应的登录名。然后，通过使用 CREATE USER 语句再次添加该登录。

通过

在 SQL Server 实例之间传输登录名和密码

简介

详细信息

目标服务器（服务器 B）上的步骤：

注解

参考

反馈

其他资源