로그인시 접속자 로그가 남게 Trigger 생성

 

- 로그를 저장할 특정 테이블이다.

CREATE TABLE DBO.DBLOGIN_LOG_TBL

(

LOGIN_NAME NVARCHAR(100)  NULL,

LOGIN_IP_ADDRESS NVARCHAR(100) NULL,

LOGIN_AUTH NVARCHAR(30) NULL,

LOGIN_DATE NVARCHAR(10) NOT NULL,

LOGIN_TIME NVARCHAR(8) NOT NULL,

)

 

DELETE DBLOGIN_LOG_TBL

 

CREATE INDEX IX_DBLOGIN_LOG_TBL ON DBLOGIN_LOG_TBL (LOGIN_DATE ASC)

 

 

--트리거생성

CREATE TRIGGER TRG_LOGON_DBLOGIN

ON ALL SERVER FOR LOGON

AS

 

BEGIN

                                                                      

                                                                                                         

DECLARE @EVENTLOG XML

SET @EVENTLOG = EVENTDATA()

                                                                                                                           

 

                                                                                                                           

INSERT TEMP__DB.DBO.DBLOGIN_LOG_TBL

 

SELECT

@EVENTLOG.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(60)') AS LOGIN_NAME,

@EVENTLOG.value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(20)') AS LOGIN_IP_ADDRESS ,

@EVENTLOG.value('(/EVENT_INSTANCE/LoginType)[1]','nvarchar(40)') AS LOGIN_AUTH,

CONVERT(NVARCHAR(10),GETDATE(),126) AS LOGIN_DATE,

CONVERT(NVARCHAR(8),GETDATE(),108) AS LOGIN_TIME

 

                                                                                                         

END

 

 

 

 

--트리거삭제

DROP TRIGGER TRG_LOGON_DBLOGIN ON ALL SERVER

 

 

 

 

 

--아래쿼리를이용하려했지만SYS.DM_EXEC_CONNECTIONS ,SYS.DM_EXEC_SESSIONS 을실행하려면접속자가실행시킬권한이있어야했다.

                       

INSERT DBLOGIN_LOG_TBL

SELECT

S.ORIGINAL_LOGIN_NAME AS LOGIN_NAME,

S.HOST_NAME AS LOGIN_HOST_NAME,

C.CLIENT_NET_ADDRESS AS LOGIN_IP_ADDRESS,

C.CLIENT_TCP_PORT AS LOGIN_TCP_PORT,

C.AUTH_SCHEME AS LOGIN_AUTH,

CONVERT(NVARCHAR(10),GETDATE(),126) AS LOGIN_DATE,

CONVERT(NVARCHAR(8),GETDATE(),108) AS LOGIN_TIME,

S.PROGRAM_NAME AS LOGIN_PRG_NAME

 

FROM SYS.DM_EXEC_CONNECTIONS (NOLOCK) AS C,

SYS.DM_EXEC_SESSIONS (NOLOCK) AS S

WHERE C.SESSION_ID= S.SESSION_ID

AND ORIGINAL_LOGIN_NAME = ORIGINAL_LOGIN()

AND C.SESSION_ID  = @@SPID