SQL Server LOGON Trigger

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;

USE TEST
GO
create table LogHistory_Server
(LogonTime DATETIME,
SystemUser VARCHAR(50),
DbUser VARCHAR(50),
SPID INT
)
 
CREATE TRIGGER Sample3_logon
ON ALL SERVER 
FOR LOGON
AS
BEGIN
INSERT INTO TEST.dbo.LogHistory_Server
SELECT GETDATE(),SYSTEM_USER,USER,@@SPID
END
GO

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

SELECT * FROM LogHistory_Server

The result can be seen as-

Well, this was all about LOGON triggers.

And also comments on this!!

Regards

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