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

适用于: Microsoft SQL Server 2005 Standard EditionMicrosoft SQL 2005 Server WorkgroupMicrosoft 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 上的数据库。而且,用户可能会收到以下错误消息:
用户“MyUser”登录失败。 (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_hexadecimal”“sp_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。 在此情况下,该登录名只能访问具有与“sys.server_principals”视图中的 SID 匹配的 SID 的数据库。 在从不同服务器合并这两个数据库时,可能会出现此问题。 若要解决此问题,可使用 DROP USERT 语句,从具有不匹配的 SID 的数据库中手动删除相应的登录名。 然后,通过使用 CREATE USER 语句再次添加该登录。
  • 如果你尝试使用预先编写脚本的 SQL Server 2000 登录名来创建新的 SQL Server 2012 登录名,将收到以下错误消息:

    消息 15021, 级别 16, 状态 2, 行 1
    为参数 PASSWORD 指定的值无效。 请指定有效的参数值。

    注意 你会在 SQL Server 2012 中收到此错误,因为向 CREATE LOGIN 和 ALTER LOGIN 语句提供了 16 字节的密码哈希。

    若要在运行 SQL Server 2012 的服务器上解决该问题,请创建带空白密码的登录名。 为此,请运行以下脚本:
    CREATE LOGIN [Test] WITH PASSWORD = '', SID = 0x90FD605DCEFAE14FAB4D5EB0BBA1AECC, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
    在创建包含空白密码的登录名之后,用户可以在下一次登录尝试中更改密码。

方法 3: 使用预置的 SQL Server 2000 密码登录

注意 只有在将 SQL Server 2000 迁移到受支持的更新 SQL Server 版本时,此方法才有意义。

在这种情况下,让用户使用预置的 SQL Server 2000 登录名登录到运行 SQL Server 的服务器。

注意 当用户使用预置的 SQL Server 2000 密码登录时,密码哈希将自动更新。

参考


有关如何解决孤立用户问题的更多信息,请访问解决孤立用户问题 Microsoft Developer Network (MSDN) 网站。

有关 CREATE LOGIN 语句的更多信息,请访问 CREATE LOGIN (Transact-SQL) MSDN 网站。

有关 ALTER LOGIN 语句的更多信息,请访问 ALTER LOGIN (Transact-SQL) MSDN 网站。