How to get history of logins to MS SQL Server 2005

Question

Is there any system view or tables available to get history logins to MS SQL server 2005?

If it were available, I would like to use TSQL to get information about login information for a datetime range. I have a scheduled job failed one day because of a thread deadlock exception on a table accessed by a stored procedure. By checking login information during that time, it may provide narrow-down information about what may caused the problem.

I guess there maybe some SQL server settings to enable saving login information. If there is no system history of login information by default, is there anyway to setup/confgure so that detailed login information can be saved?

Answer 1

AFAIK there is no out-of-the-box logon history in SQL Server 2005.

But, as described here and here, there are different ways to implement this functionality. Both approaches are using login audit events. One of them uses the Event Notification system of SQL Server 2005 to capture and store the information, the other one uses traces.

Answer 2

I think this is one of the rather useful new features in SQL Server 2008 - SQL Server Audit.

But as it's a new feature in SQL Server 2008 - it's not in SQL Server 2005 :-(

Marc

Answer 3

The SQL Server has basic auditing which can audit logins to the ERRORLOG. Right click on the SQL Server in the Object Explorer and select properties. Select the Security section and select "Both failed and successful logins" in the Login Auditing. Depending on how many people connect to the SQL Server the ERRORLOG will fill quite a lot. Make sure to turn in off after you find the problem.

You can also set the DEADLOCK_PRIORITY setting for the job so that it always wins the deadlock instead of failing.