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!!