SQL: Access Denied even for Admins

Occasionally you will find that your user account doesn’t have a default database associated with it, this will stop you logging into SQL using Windows Authentication regardless of being a local administrator.

To be able to log in do the following:

1. Create a local user (as you have administrative access this is fine)
2. Add the user to local administrators and to the SQL Server Users group something like this (SQLServer2005MSSQLUser$MACHINENAME$INSTANCENAME)
3. Restart the SQL Server
4. Log into the laptop as the new local user
5. Run SQL Server
6. Go to security and right click on the user account which does not have access to SQL Server and select properties
7. Set the default database as master
8. Set the user role as required
9. Ensure that the user has access to the correct databases
10. Click ok
11. Log out of the pc
12. Log in as the original user.

You will now have access to log into the SQL Server.