SQL Login Hell

No apologies for my thinly disguised reference to DLL hell in the post title, I think everyone dealing with SQL Server has at one point dealt with login issues (e.g. two colleagues today, hence the post). If you're like me, you'll see these issues again and again and each time you'll have to Google them to remind yourself how to solve them. I'll add to this list over time but for now, here's a couple of regulars:

Error: Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection.
This just means that you haven't set the Server to use Mixed Mode authentication. Login using Windows authentication and change your server authentication settings on your server to look like this:


Finally, restart the database engine's service. The easiest way to do this is through Control Panel > Admin Tools > Services. Click on SQL Server (Instance Name) and restart the service. You should then be able to login to the server with the sa account without a problem.

Error: Login failed for user 'sa'. (Without any other message)
Check that:

• - the sa account's password is correct or that it is strong (if enforce password policy is checked)
• - the account is granted permission to connect to the server
• - the account is enabled
• - the account is not locked

Error: Cannot open user default database. Login failed. Login failed for user 'user.name'.
I logged in with Windows authentication and dropped the database that was set as the default database for this authentication. I also forgot the sa account's password so I couldn't log in with that to change my Windows authentication's default password. That leaves scripts! Using the master database's context, execute the following command (replacing the names where appropriate):

ALTER LOGIN [user.name] WITH DEFAULT_DATABASE = master

A little special Olympics, but I managed to do this. Or perhaps that just says something about me...