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