ddl.trig | logon

create table myTest
(LogonTime datetime,
LoginName varchar(50),
ClientHost varchar(50),
LoginType varchar(50)
)
 go
;create TRIGGER myTest_LogonTrigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
       declare @LogonTriggerData xml,
                     @EventTime datetime,
                     @LoginName varchar(50),
                     @HostName varchar(50),
                     @LoginType varchar(50)
       set @LogonTriggerData = eventdata()
 
      
       set @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
       set @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
       set @HostName = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
       set @LoginType = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(50)')
 
       if @LoginType = 'SQL Login' and @HostName not in ('')
              and @LoginName in ('sa') OR @LoginName LIKE '%BACKUP%' OR @LoginName LIKE '%admin%'
              insert into master..myTest values (@EventTime, @LoginName, @HostName, @LoginType)
end
go
Comments