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 트리거 생성이 가능합니다.