Yesterday we had a user receiving the infamous failed login attempt error (no, that's not a valid IP):

Login failed for user 'MyDomain\SomeUser'. [CLIENT: 192.168.1.201]
Error: 18456, Severity: 14, State: 16.

Verifying the account in Active Directory, we saw no issues. Looking in the Security event log on the server where SQL Server was running, we saw that the operating system had authenticated the user properly. We knew the user was a member of a group that had access to the SQL Server. So at first, there was some head scratching as to what was going on. Now we make heavy use of domain security groups and it isn't unusual for a given user to have multiple security groups with access to a particular SQL Server. This user was no exception. When I saw the error and saw that everything else looked right, I suspected that one of the logins had a bad default database. My suspicions grew when we explicitly added the user's Windows account as a login to the SQL Server and the user was able to log in.

Now our policies say we're to use security groups, so we couldn't leave the user with access via this method (you don't really want to in a large environment because it makes clean-up difficult). I really suspected a bad default database configuration, so we removed the Windows user login and had the user retry. Sure enough, the error was back. At that point, I asked the user to specify the default database in the configuration. If you are using SQL Server Management Studio, you can do this by specifying the connection options, which you get to by clicking the Options button:

Then click on the Connection Properties tab and manually enter the database name:

If you click the drop down, the client will attempt to connect to the SQL Server with the default database and if there is an invalid default database, you'll get that login error. That's why the name has to be typed in by hand. Using master is a good choice, because every login should have access to the master database. Once the user manually specified the database, he was able to connect. My suspicions were confirmed.

So that raised the question, "Which login was the guilty one?" The user was a member of several security groups and with nesting, it was proving a little time consuming to determine exactly which one was causing him to break. A simple query reveals which logins are not set to use the master database as the default database:

SELECT [name], default_database_name
FROM sys.server_principals
WHERE default_database_name <> 'master';

And from there, it was a simple matter of checking a handful of logins. One such login, corresponding to a domain security group which the user happened to be a member of, was set to a default database which the login did not have access to. None of the user's other logins had access to that database, either. We had our login to fix. It was set where it had access to its default database, and the login problem was solved.