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

项目
09/06/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：在服务器 B) (目标SQL Server计算机上重置密码。

要解决此问题，请在 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，然后运行步骤 4 中生成的脚本， (从服务器 A 输出 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）上的步骤：

备注

参考

反馈

其他资源