locked
Logon triggers generate multiple records when logon one time RRS feed

  • Question

  • Hi,

    I create a logon trigger in SQL Server , below is the code:

    Firstly create my own table: select * into sessioninfo from sys.dm_exec_session where 1 = 2

    CREATE TRIGGER Test_Save_Session_Info
    ON ALL SERVER WITH EXECUTE AS 'sa'
    FOR LOGON  
    AS  
    BEGIN    
        insert into sessioninfo 
    select * from sys.dm_exec_sessions where session_id = @@spid

    END

    When I try to use normal user 'test' to logon, the trigger seem execute multiple times, because I see three records in sessioninfo table, the session_id is all different, and the login_name in the table is all 'sa', the original_login_name is all 'test', so how do I know which session_id belong to the login user 'test' ?

    Wednesday, June 22, 2016 11:03 AM

Answers

  • Logon triggers are available in SQL 2005, starting with SP2. I really hope you are not below that...

    It doesn't matter that they log out quickly - there is no way to know that in the logon trigger that they will leave directly. There is no logout trigger which would permit you cleanup the rows. And even if there was, I am not sure that it would be a good idea - even a quick login can involve a critical action.

    Friday, June 24, 2016 12:21 PM

All replies

  • All three do. You logged in three times - you get three rows.

    "But I only logged in one time", you say. How do you know? Did you debug your code? Let me guess, you logged in through Management Studio. Which logs in a couple of times. One for the query window. One for Object Explorer. One for intellisense.

    Wednesday, June 22, 2016 2:08 PM
  • Yes, I logged in through Management Studio.

    There is an session in the three recorded is what I want to, it is the session of user test, other two sessions are object explorer and intellisense, so how can I get rid of the two sessions because I find most of information about the three recorded are same except session id(session about intellisense I can remove by the program_name, but how get rid of query window or object explorer session)

    And when I logged out use Management Studio, a session also is recorded into my session table , is it Object Explorer ?

    Thanks


    Thursday, June 23, 2016 11:09 AM
  • Why would you get rid of them? The purpose of a logon trigger is handle connections to the server, and down in the server all connections should be handled as the same. Trying to exclude some data thinking "it is the same" is a deceivable game which in the end could result in the loss of important audit records.

    Thursday, June 23, 2016 1:16 PM
  • Though I get three session records, but only one session with login_name 'test' exist in sys.dm_exec_session view, the two other sessions not exist in this view, seem the two session is useless, what I want to do is that capture any logged session which can execute some user SQLs in a period time, since the two Management Studio session just gone after test logon successed, so I don't want to add the two session. 

    And logon trigger with On All Server just available in SQL Server 2008, is there any other ways I can do this in SQL Server 2005 ?

    Thanks

    Friday, June 24, 2016 10:42 AM
  • Logon triggers are available in SQL 2005, starting with SP2. I really hope you are not below that...

    It doesn't matter that they log out quickly - there is no way to know that in the logon trigger that they will leave directly. There is no logout trigger which would permit you cleanup the rows. And even if there was, I am not sure that it would be a good idea - even a quick login can involve a critical action.

    Friday, June 24, 2016 12:21 PM
  • Thanks for your answers!

    Regrads

    Monday, June 27, 2016 1:34 AM