Logon trigger executing multiple times, for single connection.

• Leonard Rutkowski

  SSCrazy

  Points: 2668

  More actions

  January 30, 2015 at 7:56 am

  #300666

  I am trying to create a logon trigger. As I am testing this, I discovered that each time I do a connection, I get 19 rows, inserted into my audit table. I ran profiler, and I see it is going through the logon trigger multiple times, for a single connection. So, what am I doing wrong? The code is fairly simplistic, and the profiler doesn't give a clue, as to what is going on. When I look at the output, I see the spid for the first couple of connections are different, then a spid, that is different from those 2 is in the next 17 rows. But, when I do an sp_who2, that spid does not exist.

  This issue was noticed on a 2012 version, that I was first testing on, then had the same issue on a 2008 R2. I am currently testing on a 2014 version, that is doing the same thing. Is the logon trigger itself, firing, and causing this?

  I also tried using the After Logon option, and got the same issue.

  Here is the code:

  CREATE TRIGGER LogonAuditTrigger

  ON ALL SERVER WITH EXECUTE AS 'sa'

  FOR LOGON

  AS

  BEGIN

  DECLARE @Body NVARCHAR(2000),

  @Subject NVARCHAR(100),

  @SessionId SMALLINT,

  @HostName NVARCHAR(128),

  @ProgramName NVARCHAR(128),

  @CheckLogin NVARCHAR(20) ;

  SET @CheckLogin = 'ad\mylogin'

  IF ORIGINAL_LOGIN() = @CheckLogin

  BEGIN

  SELECT @SessionId = session_id,

  @HostName = host_name,

  @ProgramName = program_name

  FROM sys.dm_exec_sessions

  WHERE is_user_process = 1

  AND original_login_name = @CheckLogin

  AND session_id = @@SPID ;

  IF @HostName NOT IN('OKHost1', 'OKHost2')

  BEGIN

  SET @Body = @CheckLogin + ' login has logged in from an unauthorized server. ' + CHAR(10) + CHAR(13)

  + 'Login is only allowed from OKHost1 and OKHost2' + CHAR(10) + CHAR(13)

  + ' Session Id: ' + CAST(@SessionId AS NVARCHAR(6)) + CHAR(10) + CHAR(13)

  + ' Host Name: ' + @HostName + CHAR(10) + CHAR(13)

  + 'Program Name: ' + @ProgramName + CHAR(10) + CHAR(13) ;

  SET @Subject = @CheckLogin + ' login audit' ;

  insert dbaadmin..ler_test

  (Body) values(@Body)

  END

  END

  END;