Login History Server Trigger

use master
go

IF NOT EXISTS ( SELECT TOP 1 1 FROM master.sys.sysobjects WHERE type='U' AND name='ServerLoginHistory' )
 BEGIN
  CREATE TABLE [dbo].[ServerLoginHistory]
  (
   [SystemUser] [varchar](512) NULL,
   [HostName] [varchar](512) NULL,
   [DBUser] [varchar](512) NULL,
   [SPID] [int] NULL,
   [LoginTime] [datetime] NULL,
   [AppName] [varchar](512) NULL,
   [DatabaseName] [varchar](512) NULL
  ) 
 END
GO


IF  EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'tr_ServerLoginHistory')
 BEGIN
   DROP TRIGGER [tr_ServerLoginHistory] ON ALL SERVER
 END
GO



CREATE TRIGGER [tr_ServerLoginHistory]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON 
AS
BEGIN
   IF ORIGINAL_LOGIN() NOT IN ( 'NT AUTHORITY\SYSTEM' )
    BEGIN
     INSERT INTO [ServerLoginHistory]
          SELECT ORIGINAL_LOGIN(), HOST_NAME(),USER, @@SPID, GETDATE(), APP_NAME(), DB_NAME()
    END
END --tr_ServerLoginHistory
GO


ENABLE TRIGGER [tr_ServerLoginHistory] ON ALL SERVER
GO
        
        
--DISABLE TRIGGER [tr_ServerLoginHistory] ON ALL SERVER
--GO