The document referenced below explains all the states, this is just a summary.
| State | Description |
|
1
|
Only state returned in SQL 2000. Seems to be catch-all where no other states apply; for example Account Disabled shows up as State 1(2005). |
|
2
|
Invalid Userid |
|
5
|
SQL Account provided does not exist on SQL Server. (SQL2005) |
|
6
|
Attempt to use a windows login name with SQL Authentication |
|
7
|
Login disabled and password mismatch |
|
8
|
Password mismatch |
|
9
|
Invalid Password (i.e. did not meet minimum password criteria) |
|
10
|
Read Ref Link #2 |
|
11
|
Windows account does not exist on SQL Server or CONNECT permissions is set to DENY for the login. (SQL2005) |
|
12
|
Valid login but server access failure; CONNECT permissions is set to DENY for the login. |
|
13
|
SQL Server service paused |
|
14
|
???? |
|
16
|
User doesn’t have permission to target (connection string) or default database (SQL Server login setting) (SQL 2005) |
|
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) |
|
58
|
SQL Server is set to Windows Only Authentication, SQL Login not allowed.(SQL “Denali”) |
In SQL 2005 it reports as state 1, user is not associated with a trusted account.
Ref Link #1: States Explained, http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
Ref Link #2: KB925744, Issue with State 10, http://support.microsoft.com/?id=925744
Ref Link #3: Troubleshooting: Login Failed for User ‘x’, http://msdn.microsoft.com/en-us/library/ms366351.aspx
Updated: March 6th, added state 38/40 for SQL 2008.
Updated: March 12th, added a bit more detail about state 11/12 and added state 1.
Updated: April 14, 2011, added state 58. Updated state 5, 11, & 12. If I added (Version); it means I have confirmed/found it on that version.
Updated: November 22, 2011, updated the state 8, what is invalid password?
SQL Canada (SQLCAN) 