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 |