Error handling in logon trigger

• Erin Ramsay

  SSCrazy Eights

  Points: 9728

  More actions

  June 19, 2014 at 11:51 am

  #287843

  I have a logon trigger in place that logs logons (imagine that) and inserts the values into a table. It works fine on most servers but errors out on one particular server. Research seems to indicate that try-catch will not function in this case due to the transaction inability to rollback on error.

  Couple of questions..

  1) How can I find out WHAT the error is in the insert that's breaking since it's working on eventdata() that's only instantiated during logon

  2) Since try-catch isn't going to function in this case what can I do to suppress that type of error in the future and log that there was an error in the logon insert instead of just blocking logons?

  here is the code I'm currently using

  CREATE TRIGGER [AuditServerAuthentication] ON ALL SERVER

  WITH EXECUTE AS 'TriggerUser'

  FOR LOGON AS

  BEGIN

  DECLARE @event XML

  ,@Logon_Name VARCHAR(100)

  DECLARE @QueryText TABLE (

  EventType VARCHAR(30)

  ,Parameters SMALLINT

  ,EventInfor NVARCHAR(4000)

  )

  DECLARE @SPID VARCHAR(100)

  DECLARE @Query NVARCHAR(4000)

  BEGIN TRY

  SET @Event = EVENTDATA()

  SET @Logon_Name = CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100))

  IF @Logon_Name <> 'my/login'

  BEGIN

  SET @spid = (CAST(@event.query('/EVENT_INSTANCE/SPID/text()') AS VARCHAR(100)))

  INSERT INTO @QueryText

  EXEC ('dbcc inputbuffer(' + @spid + ') WITH NO_INFOMSGS')

  SET @Query = (

  SELECT EventInfor

  FROM @QueryText

  )

  INSERT INTO mytable..logingtable (

  loginTime

  ,loginEvent

  ,loginName

  ,loginClientHost

  ,appName

  ,loginSPID

  ,QueryText

  )

  VALUES (

  CAST(replace(CAST(isnull(@event.query('/EVENT_INSTANCE/PostTime/text()'),cast(getdate() as varchar)) AS VARCHAR(64)), 'T', ' ') AS DATETIME)

  ,CAST(isnull(@event.query('/EVENT_INSTANCE/EventType/text()'),'WTF') AS VARCHAR(100))

  ,CAST(isnull(@event.query('/EVENT_INSTANCE/LoginName/text()'),'Unimind') AS VARCHAR(100))

  ,CAST(isnull(@event.query('/EVENT_INSTANCE/ClientHost/text()'),'HOST') AS VARCHAR(100))

  ,isnull(APP_NAME(),'BlackHole')

  ,CAST(isnull(@event.query('/EVENT_INSTANCE/SPID/text()'),'0') AS VARCHAR(100))

  ,@Query

  )

  END

  END TRY

  BEGIN CATCH

  INSERT INTO mytable..logingtable (

  loginTime

  ,loginEvent

  ,loginName

  ,loginClientHost

  ,appName

  ,loginSPID

  ,QueryText

  )

  VALUES (

  current_timestamp

  ,'Login Trigger'

  ,'Login Trigger'

  ,'Login Trigger'

  ,'Login Trigger'

  ,0

  ,'Login Error'

  )

  END CATCH

  END

  GO

  [/CODE]