This blog is powered by subText which runs MS SQL database in the back, which also requires me to remote manage the database from time to time when something need to be tweaked behind the scene.Â But recently, I was having trouble logging in through Microsoft SQL Server Management Studio.Â The login attempt was rejected due to the following reason.
Cannot open user default database. Login failed.
Login failed for user â€˜UserName’. (Microsoft SQL Server, Error: 4064)
My first thought is where my default database gone.Â I used to have two DB associated in my account with the one that powers this blog as default.Â But recently the hosting company, Webhost4Life, decided to bump the space to 100G while reducing the number of MS DB allowed in my package to one per account.Â For that reason, I guess they may have massed up with user’s default database setting while making these changes.Â So I filed a support ticket asking why it happened and they responded promptly but instead of simply reset the default database for me they pointed me to this post over on sqlauthority.com, which helped me to solve this issue.Â Credit goes to Pinal Dave though.
Here is the fix in short:
First of all, you need to change the connect to database to something other than <default>, like master.Â You need to click Option button on the login window to get the Connection Properties tab to show up.
Then,Â once you log in, open a new query and run the following script to reset the default database to your account.Â Make sure to fill up the [test] and master with the right value.
ALTER LOGIN [test] WITH DEFAULT_DATABASE = master
That’s it.Â Mystery was solved.