LOGON DDL Trigger를 이용한 로그인 정보 기록 남기기

한대성

SQL Server MVP

에이디컨설팅 | SQLLeader.com

 

 

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

 

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

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

 

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

USE master GO   IF EXISTS (SELECT * FROM sys.tables WHERE type = 'U' AND Name = N' Login_Info_Table ')         DROP TABLE Login_Info_Table GO   CREATE TABLE Login_Info_Table (         PostTime datetime,            -- 로그온 한 시간         SID varbinary(85),            -- 로그인에 대한 보안 아이디(Security ID)         ClientHost nvarchar(100),     -- 연결을 한 Client의 Host ID.                                       -- 서버에서 연결할 경우 ‘’으로 저장됨         LoginName nvarchar(100),      -- 로그온 명         LoginType nvarchar(30),       -- 로그온 유형         IsPooled tinyint              -- 연결 풀링 여부 ) GO

 

 

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

IF EXISTS (SELECT * FROM sys.server_triggers WHERE NAME = 'Trg_Logon_Info')         DROP TRIGGER Trg_Logon_Info on ALL SERVER GO   CREATE TRIGGER Trg_Logon_Info ON ALL SERVER FOR LOGON AS BEGIN         DECLARE @data XML         SET @data = EVENTDATA()           INSERT INTO Login_Info_Table(PostTime, SID, ClientHost,                         LoginName, LoginType, IsPooled)         SELECT GETDATE(),                @data.value('(/EVENT_INSTANCE/SID)[1]', 'varbinary(85)'),                @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(100)'),                @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'),                @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(30)'),                @data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'tinyint') END GO

 

 

트리거 생성 확인

SELECT * FROM sys.server_triggers GO

 

트리거 삭제

DROP TRIGGER Trg_Logon_Info on ALL SERVER GO

 

 

 

 

결과 확인

 

 

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

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

 

 

 

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