Sql-server – How to get history of logins to MS SQL Server 2005


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?

Best Solution

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.