How Do I See the Full Error Message?
The SQL Server error log on the instance you are trying to connect to will have any errors that SQL Server generated about the logon failure. If SQL rejected the connection/failed the logon for some reason it will generate an 18456 error typically. On the SQL side, this error will have an error state that gives clues to the cause of the error. On the client side, however, you may or may not see the useful details - it likely that you'll just see an error state of 1.
To see the full error message it is best to look at the SQL Server that you are trying to connect to and look in the SQL Server Error Log. You should see the error for the connection attempt if the connection is making it to SQL Server. From there the information is more useful than some other error messages if you know where to look.
What If There is no Error in the SQL Server Error Log?
This could be caused by several factors but it is quite likely that you are not dealing with a logon failure. Logon failures do a good job of presenting themselves in the error log. In this case it is quite possible that you are dealing with a failure to connect to SQL Server. You can troubleshoot this problem first and at least verify that you are connecting to SQL Server in the first place and that there are no issues here. Rather than get into all the potential steps for troubleshooting that here in this answer, this Technet Twiki article is an excellent resource to at least get you started.
How do I Read the Error Messages in the Log?
There are several resources for the meaning of the various error states and error messages. The below is pasted from this msdn link to get you started with some of the more common states of the 18456 error. These states may change in future versions or in the version you are on so it is a good idea to pay attention to both the message and the state and to dig deeper to understand the issue.
State ---- Meaning
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.
There are many other states as well and in edits I'll add more or in other answers you'll see more. This link has a lot of other states, most of which are valid for most versions of SQL Server above 2000. The important take away here is to look in your error log on the server side and read the message - with logon failures the messages tend to be useful a lot more often than not.
And again State 1 is a generic message on the client.
Other Errors
There are other errors for SQL Server login or connection failures. In future edits or answers from other people more information can be added as well.
What Do I Do With That Data?
It depends..
The Obvious States
States 2 & 5 signify an invalid User ID. Check the user you are working with. Does your login exist? Are you specifying the database and does the login exist as a user - has it been mapped as a user in that database.
State 6 - Oops! You are trying to pass an Active Directory login name in a manner that uses SQL authentication.. Basically trying to pass Login Name of 'FunDomain\Mwalsh' but you are connecting with SQL authentication, not trusted. This is sort of like a 2 or a 5, just a bit more descriptive.
State 7 - Login is disabled.. See where it is disabled, why it is disabled and rectify the situation.
States 8 & 9 - Bad password. Is it mistyped? Has the password changed?
State 18 - I've never encountered this one in the wild. But it is telling us that our password is set to be changed. I suppose you could see this if you are connecting from an application that uses SQL authentication (like Great Plains...) and have setup a new account with a password that needs to be changed on first use - but your application doesn't understand that or have a functionality to change the password. If you just use SSMS, you just get a dialog to change the password and all is well.
Less Obvious States
11 & 12 sort of mean the same thing- These are ones you have to pay attention to the message closely with.
Is the word "Token" in the error message? Like "Login failed for user 'myDomain\UserName'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors."
This can be a case of your SQL Server Service running as local system instead of a domain account with access to domain info potentially.
It can also be a case of needing to delegate authority through kerberos - for instance when accessing a Linked Server through Windows Authentication and passing the credentials - that double hop requires Kerberos, not NTLM. So you'll need to work through Kerberos.
Even this message can also mean other things - I've seen this recently after a migration to a server with an already existing logon - it was a very old logon at my client's environment. Dropping and recreating the logon resolved the issue. Why? It could be a few things but this post from Simon Sabin a couple years ago is an excellent resource here.
UPDATE After answering this I wanted to look an error code about default databases and I found a great blog post from Aaron Bertrand who answers a lot of questions here :-) Go check out his resource for more error messages including many that aren't in SQL Server 2012 or 2008 but still in the versions in use today. Great stuff there :-)
Off guess you are missing the connect permission. I'd always thought that was created with the user but there may be conditions where it isn't added.
Try running
USE [MyDatabase];
GO
GRANT CONNECT TO [MyDomain\MyAccount];
You could also try changing the default schema of [MyDomain\MyAccount]
ALTER USER [MyDomain\MyAccount] WITH DEFAULT_SCHEMA = MySchema;
Lastly you can use my sp_dbpermissions and sp_srvpermissions to review the permissions for the user.
EXEC sp_srvpermissions 'MyDomain\MyAccount'; -- Return instance level permissions
EXEC sp_dbpermissions 'All','MyDomain\MyAccount'; -- Return DB level permissions for all databases
Best Answer
In my case I was able to narrow down my trouble by working with my sysadmin about how the group was defined.
Knowing that the SQL Server was hosted on a machine that had joined another domain and was running with a user account on that other domain, we looked at the Group Scopes in active directory.
My user could authenticate properly after the scope was changed to universal.
If I find an authoritative source on SQL Server's behavior with respect to these domain group types, I'll be sure to update this answer.