[MSSQL] MSSQL2000 사용자계정 추출

서버 이관 시, 데이터베이스 엔진의 데이터베이스는 백업해서 이관하면 되지만

SQL Server로그인 유저는 따로 추출을 해야 한다.

Microsoft도큐먼트에 2012?였던가 추출스크립트가 제공되어있던것 같은데

MSSQL2000에서는 제공해준 스크립트가 실행이 안된다.

테이블 구조라든지 여러가지 개정이 되었기때문에.

 

MSSQL2000에서 SQL Server로그인 유저를 추출하는 스크립트는 이하와 같다.

DECLARE @login_name sysname
select @login_name = null
 
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
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
                 SELECT @txtpwd = REPLACE(UPPER(master.dbo.fn_varbintohexstr(@binpwd)),'0X','0x')
                IF (@xstatus & 2048) = 2048
                     SET @tmpstr = 'SET @pwd = CONVERT (varchar(256),' + @txtpwd + ')'
                ELSE
                     SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256),' + @txtpwd + ')'
                     PRINT @tmpstr
                    SELECT @SID_string = REPLACE(UPPER(master.dbo.fn_varbintohexstr(@SID_varbinary)),'0X','0x')
                    SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
                  END 
          ELSE BEGIN
           -- Null password
        SELECT @SID_string = REPLACE(UPPER(master.dbo.fn_varbintohexstr(@SID_varbinary)), '0X','0x')
            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