<![CDATA[Enjoy]]>

引用

来自：http://topic.csdn.net/t/20060704/17/4860219.html
在源   SQL   Server   上运行以下脚本。此脚本可在   master   数据库中创建名为   sp_hexadecimal   和   sp_help_revlogin   的两个存储过程。请在完成过程的创建之后继续执行第   2   步。

  注意：下面的过程取决于   SQL   Server   系统表。这些表的结构在   SQL   Server   的不同版本之间可能会有变化，请不要直接从系统表中选择。
  -----   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   和密码的登录。保存输出，然后将其粘贴到目标   SQL   Server   上的查询分析器中，并运行它。例如：EXEC   master..sp_help_revlogin




  SP_DEFAULTDB   'cyiyun','DB_WAYUP'