Posted on Leave a comment

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 AUTHORITYSYSTEM’ )
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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.