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-

1_SQL_Server_LOGON_Trigger

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

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

2 Comments on “SQL Server LOGON Trigger”

  1. Hi Piyush, I am working as a SQL DBA.Our requirement is we shold get notify by mail when a user tries to connect from differnet machine(IP address).It would be great if you can provide me a LOGON trigger for the same.Thanks in Advance!!!!!!

Leave a Reply

Your email address will not be published. Required fields are marked *