Post reply

Logon triggers

  • May 28, 2008 at 9:43 pm

    #182914

    Hello fellow SQL buffs,

    Just a quick and probably silly question, but I am writing a logon trigger which executes a stored proc each time a user logs onto the server. The stored proc then inserts connection info about the session to a table.

    My code is as follows:

    CREATE TRIGGER login_trigger ON ALL SERVER

    FOR LOGON

    AS

    commit

    BEGIN

    declare @SessID varchar (5)

    set @SessID = @@spid

    exec BigBro.dbo.LogUserActivity @SessID

    END

    For some reason it seems to log multiple entries for the same SPID multiple times for the one connection, rather than just the one time when the connection is established. Does SQL server constantly reinitialise it's connection once a user is logged in? I wouldn't have thought the logon trigger should be triggered more than once for a particular session.

    If anyone can explain why SQL behaves this way, it would be much appreciated.

    Thank you.

  • May 28, 2008 at 11:43 pm

    #821582

    Well 1), yes, SQL Server does reuse SPIDs/Sessions within a Connection by reinitializing them (this is probably what you are seeing),

    and 2) you can multiple sessions with the same SPID at the same time on the same connection(they show up with different "ECID"s under sp_Who). This appears to be some kind of high-level threading adn I do not know if each ECID goes through the Login triggers or not.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • May 29, 2008 at 12:30 am

    #821595

    Could you show us what LogUserActivity does?

    Does the the app that uses this DB use Connection Pooling?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 29, 2008 at 4:06 am

    #821661

    Thanks Rbarry, I think you may be right. Intersting that it works this way.

    The stored proc just grabs a whole bunch of connection information based on the SPID passed via the trigger and inserts it into a table. Eg:

    select @hostname = host_name from sys.dm_exec_connections

    insert into loggingtable @hostname

  • May 29, 2008 at 5:36 am

    #821693

    There is a sid and a login time in sysprocesses. The login time stays constant when a connection is reinitialized without being disconnected, and I think the sid stays constant as well. You could store one or both of these in your table and check for duplicates before re-inserting.

    select sid, login_time from master.sys.sysprocesses where spid = @@spid

  • May 29, 2008 at 4:16 pm

    #822031

    Hello boss,

    though i couldn't solve your doubt

    I have a suggestion.

    if you want to collect statistics like which user logged at what time

    You can do like this ...

    create a table with 2 columns SPID and timestamp. make SPID column as unique. so you cannot insert duplicate rows.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply