Troubleshoot login errors with SQL Server login failed states

By | December 22, 2013

Whenever you receive a Login failed error from a SQL instance your faced with the problem  of context. In the SQL Server log you can find a more detailed description. You should see the client generating the error and the state. The table below helps to find the cause of the failed login attempt.

Error 18456, Severity 14: Login Failed States 
Login Failed states summary
State Description
1 Only state returned in SQL 2000. Server is in Single-User Mode (2005).
2 and 5 Invalid User Id
6 Attempt to use a windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid Password
10 Read Ref Link #2
11 and 12 Valid login but server access failure; Default Database access failure. Or Initial database connection failure in connection string.
13 SQL Server service paused
16 User doesn’t have permission to target database
18 Change password required
23 Server in process of shutting down, and user attempted to login.
27 Initial database could not be determined for session
38 Initial database could not be determined for session (SQL 2008)
40 Default database could not be accessed (SQL 2008)

One thought on “Troubleshoot login errors with SQL Server login failed states

Leave a Reply

Your email address will not be published. Required fields are marked *