Introduction to login triggers
The login trigger will fire the stored procedure in response to the LOGIN event. This event is raised when a user session is established with an instance of SQL Server. The login trigger will fire after the authentication phase of login is completed and before the user session is actually established. Therefore, all messages that come from within the trigger and usually reach the user (such as error messages and messages from PRINT statements) are transferred to the SQL Server error log. If authentication fails, the login trigger will not fire.