Each login in SQL Server has a default database associated with it. When you login to SQL Server context is set to default database for login. This is set when login is created and it can be changed by using ALTER LOGIN command.
If for some reason the default database is not available on server you cannot login to SQL Server, an error message is received as below:
To resolve this error you need to change the default database which the login account uses. You can do this by logging in using a system administrator account or a different account which has securityadmin privileges.
Solution 1:
In case you do not have access to a system admin account, you can change the database which is used after logging via "Connect to Server" window in SQL Server Management Studio.
1. Provide "Server name", "Login" and "Password" information:
2. Click on "Options >>>" to open "Connection Properties" tab:
3. Provide a different database name (preferably 'master') manually for "Connect to database" replacing <default>. (<Browse Server…> will not work here), and click on "Connect".
Now you should be able to login successfully. However, this solution does not address the problem permanently. You will be need to do the same exercise if you are logging in from a different machine. (It will work on same machine due to the face that SQL Server Management Studio remembers your choice..)
Solution 2:
In order to fix the issue permanently you need to change the default database for login. You can do this using SQL Server Management Studio or T-SQL. You will need an account with sysadmin or securityadmin privileges.
1. Login to SQL Server using account with appropriate privileges.
2. Locate Login with issues under Server > Security > Logins in Object Explorer.
3. Right click on Login and select "Properties" from the context-menu.
4. In the "Login Properties" window change the default database by selecting a default database and click on "OK" to apply changes:
That’s all, now you should be able to login to SQL Server without any issues.
If you are a T-SQL fan you can also change the default database for login using ALTER LOGIN command as below:
USE [master]
GO
ALTER LOGIN [Vishal]
WITH DEFAULT_DATABASE = [SqlAndMe]
GO
Results:
Command(s) completed successfully.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
Filed under: Management Studio, SQL Configuration, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012 
