(─━┘_└━─)/ 머리만1톤(Head1Ton)

한대성

SQL Server MVP

에이디컨설팅 | SQLLeader.com

Service Pack 2 이후에 몇 가지 새로운 기능들 중에서 유용한 기능을 하나 소개 드립니다.

SQL Server 2005에 추가된 DDL 트리거 중, 로그온과 관련된 이벤트가 추가되었습니다.

이를 이용하여, SQL Server에 로그인한 정보를 테이블이나 다른 형태에 별도로 남길 수 있습니다.

Logon 이벤트 정보를 기록할 테이블 생성

LOGON 이벤트에 대한 DDL 트리거 생성

트리거 생성 확인

트리거 삭제

결과 확인

이를 이용하면 Profiler나 PSSDiag, 또는 다른 Audit 툴을 이용하지 않고도 SQL Server에 로그인 한 정보를 기록을 남길 수 있습니다.

단, SQL Server 2005 Service Pack 2 이상이 설치되어야 LOGON에 대한 DDL 트리거 생성이 가능합니다.

본 게시판에 실린 글은 누구나 복사하셔서 이용하셔도 되지만, 반드시 출처(SQLLeader.com) 및 글의 링크를 밝혀주셔야 합니다. 

SQL Server 7.0 및 2000 버전에서는 다음 기술자료에 나오는 바와 같이
인스턴스간 로그인 및 암호 전송이 가능합니다.
http://support.microsoft.com/kb/246133/


SQL Server 2005에서도 인스턴스간에 로그인 및 암호를 전송하는 프로시저가 있어서 소개해 드립니다...

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)

   DECLARE @pwd varchar (256)

   IF (@login_name IS NULL)

      DECLARE login_curs CURSOR FOR

         SELECT sid, name, is_disabled, password_hash FROM master.sys.sql_logins

         WHERE type='S' AND name <> 'sa'

   ELSE

      DECLARE login_curs CURSOR FOR

         SELECT sid, name, is_disabled, password_hash FROM master.sys.sql_logins

         WHERE type='S' 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='CREATE LOGIN [' + @name +'] FROM WINDOWS'

                              PRINT @tmpstr

                              SET @tmpstr='DENY CONNECT SQL TO[' + @name +']'

                              PRINT @tmpstr

                           END

                        ELSE

                           BEGIN -- NT login has access

                              SET @tmpstr='CREATE LOGIN [' + @name +'] FROM WINDOWS'

                              PRINT @tmpstr

                           END

                     END

                  ELSE

                     BEGIN -- SQL Server authentication

                        IF (@binpwd IS NOT NULL)

                           BEGIN -- Non-null password

                              EXEC sp_hexadecimal @binpwd, @txtpwd OUT

                              EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

                              SET @tmpstr = 'CREATE LOGIN ' +@name+ ' WITH PASSWORD='+ @txtpwd+ ' HASHED,SID=' + @SID_string

                           END

                        ELSE

                           BEGIN

                              -- Null password

                              EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

                              SET @tmpstr = 'CREATE LOGIN ' +@name+ ' WITH PASSWORD='''',SID=' + @SID_string

                           END     

                     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

[따라하기]

--임의의 로그인 AAA  를생성

CREATE LOGIN AAA WITH PASSWORD='P@ssword'

GO

--로그인 생성 스크립트 생성

EXEC sp_help_revlogin

/*

/* sp_help_revlogin script

** Generated 05  3 2006  5:53PM on DSHANNOTE */

DECLARE @pwd sysname

-- Login: AAA

CREATE LOGIN AAA WITH PASSWORD=0x010032B33F8F5C29F519C7453917FEE2292CDF1068D52158AC51 HASHED,SID=0xE8FE830659D7CC4A8D3AB3EECA79911B

*/

DROP LOGIN AAA

GO

--생성된 스크립트를 이용하여 AAA 로그인복원

DECLARE @pwd sysname

-- Login: AAA

CREATE LOGIN AAA WITH PASSWORD=0x010032B33F8F5C29F519C7453917FEE2292CDF1068D52158AC51 HASHED,SID=0xE8FE830659D7CC4A8D3AB3EECA79911B

GO

--AAA 로 로그인이 되는지 테스트 수행