Posted by mikeb on August 13, 2007
Typically seen with application to SQL DB authentication. You can find more detail out on Microsoft’s support web site but here are some things to look at just off the top:To increase security, the error message that is returned to the client deliberately hides the nature of the authentication error. However, in the SQL Server error log, a corresponding error contains an error state that maps to an authentication failure condition. Compare the error state to the following list to determine the reason for the login failure.
|2||User ID is not valid.|
|5||User ID is not valid.|
|6||An attempt was made to use a Windows
login name with SQL Server Authentication.
|7||Login is disabled, and the password is incorrect.|
|8||The password is incorrect.|
|9||Password is not valid.|
|11||Login is valid, but server access failed.|
|12||Login is valid login, but server access failed.|
|18||Password must be changed.|
Other error states exist and will signify an unexpected internal processing error.So here are some other things you might want to try (as RECOVERY_PENDING is my favorite issue :p )
SELECT name, principal_id, type_desc, is_disabled, default_database_name, default_language_name
WHERE name = ‘login_name’
goSELECT name, database_id, user_access_desc, state_desc, is_in_standby
WHERE name = ‘default_db_name’
WHERE member_principal_id = suser_id( ‘login_name’ )
suser_name(grantee_principal_id), permission_name, state_desc
WHERE grantee_principal_id = suser_id( ‘login_name’ )
Pay attention to the default database, if the login is disabled and if it has at least CONNECT SERVER permission, if the server is in a different mode than ONLINE etc.