How to create a login trigger

It’s Important to check you are not blocking all users to connect to the server

You can check how trigger use on the server

select * from sys.server_triggers
CREATE TABLE LogonAuditing /* Creates table for logons inside db */
(
    SessionId int,
    LogonTime datetime,
    HostName varchar(50),
    ProgramName varchar(500),
    LoginName varchar(50),
    ClientHost varchar(50),
	eveData xml
)
GO


CREATE TRIGGER [LogonAuditTrigger] 
ON ALL SERVER WITH EXECUTE AS 'sa' 
FOR LOGON  
AS
BEGIN
DECLARE @TrgData xml =eventdata()
BEGIN TRY
if exists(select 'a' from sys.tables where name ='LogonAuditing')
INSERT INTO [dbo].[LogonAuditing]  ( SessionId, LogonTime, HostName, ProgramName, LoginName, ClientHost)
	SELECT @@spid, @TrgData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
	, HOST_NAME(), APP_NAME() 
	, @TrgData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
	, @TrgData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
END TRY
BEGIN CATCH
END CATCH
END

Other References:

Spread the love

Leave a Comment