Hello Folks,

You might have heard about it or maybe not. Don’t worry at all because I will be going to give you some of the heads up:

Logon triggers were introduced earlier with the arrival of SQL Server 2005 SP2. It is somewhat similar to the DDL trigger which I had discussed it earlier in my blogs. Here is the link for it:

Some main points regarding Logon Trigger:

  • Logon Triggers fire stored procedures or T-SQL statements in response to LOGON events.
  • Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established.
  • So all the messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log.
  • Logon triggers do not fire if authentication fails.
  • You can also use the Logon Trigger to do some specific work like:
    • To audit and control sessions.
    • Tracking Logon activity.
    • Restricting Logons to SQL Server.
    • Limiting the number of sessions for a specific logon.
  • This will be more clear to you, if you follow this example:

Well, this is an exception. You can see the same System user multiple times with different time and SPID;

Now, if you want to see login history, then write the select query;

The result can be seen as-


Well, this was all about LOGON triggers.

And also comments on this!!



Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook