GS应用数据库转移保留原用户密码操作步骤

GS应用数据库转移保留原用户密码操作步骤

 

1.         对需要转移的数据库做备份，可使用SQLSERVER 2000 提供的数据库备份方法，或使用浪潮总部提供的数据库维护工具

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. -----

 

 

3.         进入查询工具运行存储过程 “sp_help_revlogin”,将存储过程的执行结果保存至一文本文档（重要）

4.         在新的服务器中安装QLSERVER 2000，运行GS数据库安装程序中的 DBconfiger.exe安装数据库实例，注意在输入数据库实例编号时，必须与源服务器中的数据库实例号一致，

5.         进入新服务器中的SQLSERVER 企业管理器，在新建的cwbase 数据库上做数据库恢复，右键点击数据库，选择所有任务\恢复数据库，选择备份数据库所在位置，选择强制还原，点击确定。注意在此如果数据库文件较大时，使用浪潮总部提供的数据库维护工具可能会存在不能正常恢复的错误

6.         数据库恢复完毕，依次执行以下数据库脚本

 

 Declare @inst_name varchar(50)

 Declare @inst_uid int

User master

 select  @inst_name=APPinstmantab_dbnm from appinstmantab

select @inst_uid=uid from sysusers where name=(select appinstmantab_login from appinstmantab where appinstmantab_dbnm=@inst_name)

sp_configure 'allow updates',1

go

reconfigure with override

go

update cwbase..sysusers set sid=master..syslogins.sid

from cwbase..sysusers,master..syslogins

where cwbase..sysusers.name=master..syslogins.name

go

use cwbase

update sysobjects set uid=@inst_uid where uid<>'1'

Use Master

EXEC sp_configure 'allow updates', 0

reconfigure with override

7.         将在第三步中生成的sql脚本文件粘贴至查询工具中，运行，至此源库已完全恢复至新库，并且原来的用户登录信息也完整的恢复至新数据库中

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13158758/viewspace-614840/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13158758/viewspace-614840/