Chinaunix首页 | 论坛 | 博客
  • 博客访问: 273790
  • 博文数量: 33
  • 博客积分: 880
  • 博客等级: 准尉
  • 技术积分: 660
  • 用 户 组: 普通用户
  • 注册时间: 2009-04-02 22:25
文章分类

全部博文(33)

文章存档

2011年(33)

分类: WINDOWS

2011-07-26 11:24:55

MSSQL2000传递登录密码至MSSQL2005

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

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

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

   

  1. ----- Begin Script, Create sp_help_revlogin procedure -----

  2. USE master
  3. GO
  4. IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  5.   DROP PROCEDURE sp_hexadecimal
  6. GO
  7. CREATE PROCEDURE sp_hexadecimal
  8.     @binvalue varbinary(256),
  9.     @hexvalue varchar(256) OUTPUT
  10. AS
  11. DECLARE @charvalue varchar(256)
  12. DECLARE @i int
  13. DECLARE @length int
  14. DECLARE @hexstring char(16)
  15. SELECT @charvalue = '0x'
  16. SELECT @i = 1
  17. SELECT @length = DATALENGTH (@binvalue)
  18. SELECT @hexstring = '0123456789ABCDEF'
  19. WHILE (@i <= @length)
  20. BEGIN
  21.   DECLARE @tempint int
  22.   DECLARE @firstint int
  23.   DECLARE @secondint int
  24.   SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  25.   SELECT @firstint = FLOOR(@tempint/16)
  26.   SELECT @secondint = @tempint - (@firstint*16)
  27.   SELECT @charvalue = @charvalue +
  28.     SUBSTRING(@hexstring, @firstint+1, 1) +
  29.     SUBSTRING(@hexstring, @secondint+1, 1)
  30.   SELECT @i = @i + 1
  31. END
  32. SELECT @hexvalue = @charvalue
  33. GO

  34. IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  35.   DROP PROCEDURE sp_help_revlogin
  36. GO
  37. CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
  38. DECLARE @name sysname
  39. DECLARE @xstatus int
  40. DECLARE @binpwd varbinary (256)
  41. DECLARE @txtpwd sysname
  42. DECLARE @tmpstr varchar (256)
  43. DECLARE @SID_varbinary varbinary(85)
  44. DECLARE @SID_string varchar(256)

  45. IF (@login_name IS NULL)
  46.   DECLARE login_curs CURSOR FOR
  47.     SELECT sid, name, xstatus, password FROM master..sysxlogins
  48.     WHERE srvid IS NULL AND name <> 'sa'
  49. ELSE
  50.   DECLARE login_curs CURSOR FOR
  51.     SELECT sid, name, xstatus, password FROM master..sysxlogins
  52.     WHERE srvid IS NULL AND name = @login_name
  53. OPEN login_curs
  54. FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
  55. IF (@@fetch_status = -1)
  56. BEGIN
  57.   PRINT 'No login(s) found.'
  58.   CLOSE login_curs
  59.   DEALLOCATE login_curs
  60.   RETURN -1
  61. END
  62. SET @tmpstr = '/* sp_help_revlogin script '
  63. PRINT @tmpstr
  64. SET @tmpstr = '** Generated '
  65.   + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
  66. PRINT @tmpstr
  67. PRINT ''
  68. PRINT 'DECLARE @pwd sysname'
  69. WHILE (@@fetch_status <> -1)
  70. BEGIN
  71.   IF (@@fetch_status <> -2)
  72.   BEGIN
  73.     PRINT ''
  74.     SET @tmpstr = '-- Login: ' + @name
  75.     PRINT @tmpstr
  76.     IF (@xstatus & 4) = 4
  77.     BEGIN -- NT authenticated account/group
  78.       IF (@xstatus & 1) = 1
  79.       BEGIN -- NT login is denied access
  80.         SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
  81.         PRINT @tmpstr
  82.       END
  83.       ELSE BEGIN -- NT login has access
  84.         SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
  85.         PRINT @tmpstr
  86.       END
  87.     END
  88.     ELSE BEGIN -- SQL Server authentication
  89.       IF (@binpwd IS NOT NULL)
  90.       BEGIN -- Non-null password
  91.         EXEC sp_hexadecimal @binpwd, @txtpwd OUT
  92.         IF (@xstatus & 2048) = 2048
  93.           SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
  94.         ELSE
  95.           SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
  96.         PRINT @tmpstr
  97.     EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
  98.         SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
  99.           + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
  100.       END
  101.       ELSE BEGIN
  102.         -- Null password
  103.     EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
  104.         SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
  105.           + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
  106.       END
  107.       IF (@xstatus & 2048) = 2048
  108.         -- login upgraded from 6.5
  109.         SET @tmpstr = @tmpstr + '''skip_encryption_old'''
  110.       ELSE
  111.         SET @tmpstr = @tmpstr + '''skip_encryption'''
  112.       PRINT @tmpstr
  113.     END
  114.   END
  115.   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
  116.   END
  117. CLOSE login_curs
  118. DEALLOCATE login_curs
  119. RETURN 0
  120. GO
  121.  ----- End Script -----
  122.   


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

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

这里举例数据库名为bjab,数据库登录名为bjabMSSQL登录名为bjab

  1. DECLARE @pwd sysname

  2. --声明pwd变量

  3. -- Login: XNDB\sqlstart

  4. EXEC master.. ' XNDB\sqlstart '

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

  6. -- Login: bjab

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

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

  9. --导入用户名

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

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

  1. use bjab

  2. go

  3. --使用数据库

  4. sp_change_users_login 'update_one', 'bjab', 'bjab'

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

sp_change_users_login使用如下:

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

user为数据库登录名,loginMSSQL登录名。

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

  1. 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错误

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

阅读(1751) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~