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

INTRODUCTION

本文介绍如何不同服务器上的 Microsoft SQL Server 2005 实例之间传输登录和密码。

本文, 服务器 A 和服务器 B 是不同的服务器。 此外, 服务器 A 和 B 服务器都运行 SQL Server 2005。
将数据库从服务器 A 上的 SQLServer 实例移到 B, 服务器上的 SQLServer 实例后用户可能不能够登录到数据库 B 另外服务器上， 用户可能收到以下错误信息：

对于用户 MyUser ' ' 登录失败。 （MicrosoftSQLServer、 错误 18456：）

发生此问题是因为没有您做从服务器 A 上的 SQLServer 实例传输和密码登录到服务器 B 上的 SQLServer 实例
要从服务器 A 上的 SQLServer 实例传输到服务器 B, 上的 SQLServer 实例登录和密码请按照下列步骤操作：

1.在服务器 A, 启动 SQLServerManagementStudio, 并然后连接到 SQLServer 从中移动数据库的实例。

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) 和原始密码登录。

4.在服务器 B, 启动 SQLServerManagementStudio, 并再连接到的数据库移动到 SQLServer 实例。
要点 之前请您转到步骤 5, 查阅 " 备注 " 部分中信息。

5.打开新查询编辑器窗口, 并运行步骤 3 中生成输出脚本。

批注

对实例 B： 服务器上运行该输出脚本之前检查下列信息

•仔细查看脚本输出。 如果服务器 A 和 B 服务器位于不同域, 您需要修改输出脚本。 然后, 您需要用新域名 LOGIN CREATETABLE 语句中替换原始域名称。 集成登录授予新域中访问原域中没有相同 SID 作为登录。 因此, 用户是从这些登录中孤立出去。 有关如何解决这些孤立用户, 请单击下列文章编号以查看 Microsoft 知识库中相应：

240872 (http://support.microsoft.com/kb/240872/) 如何运行 SQLServer 的服务器之间移动数据库时解决权限问题

如果服务器 A 和 B 服务器位于同一域, 使用相同的 SID。 因此, 用户是不容易被孤立。

•由使用加密密码, 输出脚本中创建登录。 这是因为存在 LOGIN CREATETABLE 语句中 HASHED 参数。 该参数指定散， 是已经列是 PASSWORD 参数后输入该密码。

•通过默认, 可只是 sysadmin 固定服务器角色成员从 sys.server_principals 视图运行 SELECT 语句。 用户无法创建或运行输出脚本除非属于 sysadmin 固定服务器角色授予对用户, 必要权限。

•本文中步骤执行不传输用于特定登录默认数据库信息。 这是因为默认数据库服务器 B 上可能不总是存在 由登录名和默认数据库中传递作为参数来定义默认数据库为登录, 使用 ALTERDATABASE LOGIN 语句。

•服务器 A 的排序顺序可能区分大小写, 并且可能区分大小写排序次序的服务器 B。 此例, 用户必须键入作为大写字母传送登录后密码和密码以服务器 B 上实例中所有字母
或者, 服务器 A 的排序顺序可能是区分大小写, 并且可能区分大小写排序次序的服务器 B。 通过登录和密码， 除非满足下列条件之一是传输到服务器 B 上实例此时, 用户无法登录:

•原始密码包含没有字母。

•所有原始密码中字母是大写。

服务器 A 和服务器 B 的排序顺序可能区分大小写, 或者可能是区分大小写排序次序的服务器 A 和服务器 B。 不会在这些情况下, 用户遇到问题。

•登录服务器 B 上实例已经处于可能有一个名称， 等同于输出脚本中一个名称。 在这种情况下, B： 服务器上实例上运行输出脚本时收到以下错误信息

msg 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。 此例, 登录只能访问数据库具有 SID 匹配 SID sys.server_principals 视图中。 如果是从两个不同服务器合并两个数据库可能会发生此问题。 要解决此问题, 通过 DROPUSER 语句具有 SID 匹配数据库中手动删除登录。 再次通过 CREATE USER 语句然后, 添加登录。

from: http://support.microsoft.com/kb/918992/