The sql server error log, starting with sql 2005 +, began giving us some very descriptive reasons for the login failure. This detailed reason was not always returned to the client for security reasons, but the SQL DBA could look in the error log and get more information on the error.
DBA's are never satisfied as the more information is enough to help, but not completely. Recently we started receiving the login state 16 on a TFS (Team Foundation Server) SQL Server. State 16 means that incoming user does not have permission to log into the target database. Damn, it'd be nice if the error message would tell you the target database it was trying to login to !
Out of desperation we promoted the login to a sysadmin. The login state 16 error continued.
This is when we clued in that the target database must just not exist. Sure enough a comparison with our other TFS SQL instances showed a missing database.
Once we restored that missing database, the error went away. Frustruating to troubleshoot this particular login state, when if the error message were more clear (by including the name of the target database) we could have fixed this in 10 minutes. The TFS administrator was clue-less, and was unable to determine that there was even an error happenning, or what service was generating the error. We never did resolve that. So remember state 16 may not just be permissions to a database, it may be a missing database.