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;
create table LogHistory_Server
CREATE TRIGGER Sample3_logon
ON ALL SERVER
INSERT INTO TEST.dbo.LogHistory_Server
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!!