What happens if you drop a user's default database in SQL Server 2005 and the user then tries to use SQL Server Management Studio? You get this crystal clear error message:
What to do? If you have another working administrative login, then you simply need to use that to update the properties of the user that can't connect:
But what if the user that can't connect is the only administrative user? In that case, you need to tell SQL Server Management Studio to connect to a different database on startup, which is done using command line options:
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" -S SQLSERVER2005 -d master -E
Here's all the command line options:
After executing the above command, SQL Server Management Studio will come up without the object explorer (the tree view on the left) open. But, it will be connected to the master database and will allow you to run queries. To fix the user login so that the object explorer can be opened, simply run a query to change the user's default database:
sp_defaultdb 'SQLSERVER2005\dbuser', master
You will be then be able to open the object explorer by using File -> Connect Object Explorer...
Appendix - exact message (for search engines):
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)