[MSSQL] 로그온 트리거 (Login Trigger)

SQL 접속시 언제 누가 로그온 했는지 기록을 남겨야 했다

간단하게 Login Trigger 를 사용하여 DB 에 기록을 남겨보도록 하겠다

---

1. 접속 기록을 보관할 테이블 생성

 

USE DatabaseName

CREATE TABLE [dbo].[TableName](
	[SessionId] [INT] NULL,
	[SID] [VARCHAR](50) NULL,
	[LogonTime] [DATETIME] NULL,
	[ProgramName] [VARCHAR](50) NULL,
	[HostName] [VARCHAR](50) NULL,
	[ClientHost] [VARCHAR](50) NULL,
	[LoginName] [VARCHAR](50) NULL,
	[EventType] [VARCHAR](10) NULL,
	[ServerName] [VARCHAR](50) NULL,
	[LoginType] [VARCHAR](50) NULL,
	[IsPooled] [INT] NULL
) ON [PRIMARY]

 

 

2. 로그온 트리거 생성

 

CREATE TRIGGER TriggerName
ON ALL SERVER FOR LOGON
AS
BEGIN
	DECLARE	@LogonTriggerData xml
			, @SPID		INT
			, @SID		VARCHAR(50)
			, @LogonTime	DATETIME
			, @ServerName	VARCHAR(50)
			, @ClientHost	VARCHAR(50)
			, @LoginName	VARCHAR(50)
			, @EventType	VARCHAR(10)
			, @LoginType	VARCHAR(50)
			, @IsPooled	INT
			
	SET @LogonTriggerData = eventdata() 		
	SET @SPID = @LogonTriggerData.value('(/EVENT_INSTANCE/SPID)[1]', 'INT')						-- SPID      
	SET @SID = @LogonTriggerData.value('(/EVENT_INSTANCE/SID)[1]', 'VARCHAR(50)')				-- SID
	SET @LogonTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'DATETIME')		-- POSTTIME														-- PROGRAM_NAME
	SET @ServerName = @LogonTriggerData.value('(/EVENT_INSTANCE/ServerName)[1]', 'VARCHAR(50)')	--ServerName
	SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'VARCHAR(50)') 	--IP
	SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(50)')	--LOGINNAME
	SET @EventType = @LogonTriggerData.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(10)')	--EventType
	SET @LoginType = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'VARCHAR(50)')	--LOGINTYPE
	SET @IsPooled = @LogonTriggerData.value('(/EVENT_INSTANCE/IsPooled)[1]', 'INT')				-- 연결 폴링을 사용하여 연결 1 / 아닐경우 0

	IF program_name()='.Net SqlClient Data Provider' OR program_name()='Microsoft SQL Server Management Studio'
        BEGIN 

			INSERT INTO PERF.dbo.TableName
			( 
                	[SessionId]
                	, [SID] 
                	, [LogonTime]
                	, [ProgramName]
                	, [HostName] 
                	, [ClientHost]
                	, [LoginName]
                	, [EventType] 
                	, [ServerName] 
                	, [LoginType]
                	, [IsPooled] 
			) 
			SELECT	@SPID
                    , @SID 
                    , @LogonTime
                    , program_name()
                    , HOST_NAME()
                    , @ClientHost
                    , @LoginName
                    , @EventType
                    , @ServerName
                    , @LoginType
                    , @IsPooled

		END
END
GO

 

 

[참고 - 트리거 (Trigger) 잘못 설정하였을때]

[MSSQL] 트리거(Trigger) 잘못 설정하여 로그인 되지 않을 때