MSSQL实例之间传递登录和密码

MSSQL2000传递登录密码至MSSQL2005

在将MSSQL2000数据库迁移至MSSQL2005的时候遇到了问题，数据库可以复制过去附加，但是MSSQL的登录名怎么导过去呢，总不能登一台一台的WEB服务器去查看数据库连接文件然后在MSSQL2005中新建登录把，这样对于数据库少的MSSQL还可以，但是如果你有几十个数据库的话，那样就太麻烦，而且还浪费时间。

下面是Microsoft的一个解决方案，在版本不同的MSSQL之间传递数据库的登录和密码，操作步骤如下：

1、  在源 SQL Server 上运行以下脚本。此脚本可在 master 数据库中创建名为 sp_hexadecimal 和 sp_help_revlogin 的两个存储过程。请在完成过程的创建之后继续执行第 2 步。

   

----- Begin Script, Create sp_help_revlogin procedure -----

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(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
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 @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR
    SELECT sid, name, xstatus, password FROM master..sysxlogins
    WHERE srvid IS NULL AND name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR
    SELECT sid, name, xstatus, password FROM master..sysxlogins
    WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
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 ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@xstatus & 4) = 4
    BEGIN -- NT authenticated account/group
      IF (@xstatus & 1) = 1
      BEGIN -- NT login is denied access
        SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
        PRINT @tmpstr
      END
      ELSE BEGIN -- NT login has access
        SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
        PRINT @tmpstr
      END
    END
    ELSE BEGIN -- SQL Server authentication
      IF (@binpwd IS NOT NULL)
      BEGIN -- Non-null password
        EXEC sp_hexadecimal @binpwd, @txtpwd OUT
        IF (@xstatus & 2048) = 2048
          SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
        ELSE
          SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
        PRINT @tmpstr
    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
          + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
      END
      ELSE BEGIN
        -- Null password
    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
          + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
      END
      IF (@xstatus & 2048) = 2048
        -- login upgraded from 6.5
        SET @tmpstr = @tmpstr + '''skip_encryption_old'''
      ELSE
        SET @tmpstr = @tmpstr + '''skip_encryption'''
      PRINT @tmpstr
    END
  END
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
  END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
 ----- End Script -----

2、   在创建 sp_help_revlogin 存储过程后，请从源服务器上的查询分析器中运行 sp_help_revlogin 过程。sp_help_revlogin 存储过程可同时用于 SQL Server 7.0 和 SQL Server 2000。sp_help_revlogin 存储过程的输出是登录脚本，该脚本可创建带有原始 SID 和密码的登录。保存输出。

3、   在导出的文档中我们会看到如下内容，简单解释下：

这里举例数据库名为bjab，数据库登录名为bjab，MSSQL登录名为bjab

DECLARE @pwd sysname

--声明pwd变量

-- Login: XNDB\sqlstart

EXEC master.. ' XNDB\sqlstart '

--设置操作用户，根据新服务器的名称和MSSQL的用户修改这里，用户可以使用agent用户

-- Login: bjab

SET @pwd = CONVERT (varbinary(256), 0x0100C87A7A3FC44E737276347FA21B48E87984FBXD93ACF8C576DDBC26C269CFE1E1BE8BCDBF22E92A0C89984520)

EXEC master..sp_addlogin 'bjab', @pwd, @sid = 0x290A6FA15F7F467B9X5850575ECC445F, @encryptopt = 'skip_encryption'

--导入用户名

4、   执行完上述语句，我们就成功的导入了登录名bjab，但是导入了bjab，我们发现不能登录数据库，这是数据库bjab里面已经存在了一个bjab的登录名，但是MSSQL的登录名和数据库本身的登录名还没有联系起来，这个主要是解决一个叫做孤立用户的问题。

由于我们导入的登录名和数据库库bjab的登录名bjab具有同样的sid，所以直接运行如下语句进行关联：

use bjab

go

--使用数据库

sp_change_users_login 'update_one', 'bjab', 'bjab'

--MSSQL登录名和数据库登陆名进行关联

sp_change_users_login使用如下：

sp_change_users_login[ [ @UserNamePattern = ] 'user' ][ , [ @loginName = ] 'login' ]

user为数据库登录名，login为MSSQL登录名。

5、   但是接下来你会发现，我们使用用户名可以远程登上数据库了，但是网站却依然无法访问，这是因为我们MSSQL登录名的默认数据库是master，使用以下语句更改登录的默认数据库。

sp_defaultdb 'bjab','bjab'

 

sp_defaultdb使用如下：

sp_defaultdb [ @loginame = ] 'login' ,[ @defdb = ] 'database'

login为登录名，database为数据库名。

 

OVER了，这样就没有问题了，但是在实际处理过程中你还会遇到这样的那样的问题，总结如下：

 

1、 MSSQL登录名无法导入

请查看MSSQL安全性-登录，最好刷新一下，看是否存在此用户，若存在，删除后重新导入。

2、 MSSQL登录名无法与数据库登录名进行关联

这个问题原因一般是数据库登录名已经与别的MSSQL登录名进行了关联，或者MSSQL登录名与别的数据库登陆名进行了关联，请取消这些关联，然后重新进行你所要的关联

3、 JSP网站报错（TOMCAT环境），处理SQL错误

哎，写太多了，手疼，看我blog把。

TOMCAT配置MSSQL2005连接池

4、 ASP网站报错（IIS环境），处理SQL错误

这个问题明天单独发给大家，貌似很麻烦滴