How can I monitor user login events in SQL Server?

Data and Databases
1

I need to track when users log in to our SQL Server so I can analyze both average traffic and peak concurrent access. Does SQL Server offer any built‐in view or hidden table that records these events? Our application isn’t logging this data on its own. I’m currently working with Microsoft SQL Server 2000 and will soon migrate to SQL Server 2005, so any guidance that works for both versions would be very useful.

Example query:

SELECT AccessDate, UserIdentifier, SessionCode
FROM dbo.UserLoginHistory
WHERE AccessDate > DATEADD(DAY, -1, GETDATE());
2

Based on personal experience, SQL Server does not provide a built‐in table that logs user login details. For a solution that works with both SQL Server 2000 and 2005, I have found that implementing a logon trigger is effective. A logon trigger can capture login events and record details such as login time, user name, and session information to a custom table. This method allows you to analyze average traffic and concurrent sessions. Alternatively, for more detailed analysis in SQL Server 2005, you might consider server-side traces or the Profiler tool, but the trigger approach offers a lightweight and customizable solution.