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

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

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