Logon triggers were introduced in SQL server 2005 SP2. They fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. You can extract useful XML data about Logon events inside Logon by using the EVENTDATA function. I will demonstrate a little later how we can use this function to audit log-ins to a user table.
I got the idea for this article when one of our clients complained that their server’s performance was degrading during business hours. They thought it was weird that at the same time, SQL Server would list more than 1200 connections on SQL server Activity Monitor. This is how to fix it.