Cannot Open User Default Database

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)

image

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.

 image

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
GO

That’s it.  Mystery was solved.

7 thoughts on “Cannot Open User Default Database

  1. I don’t know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.

  2. This exercise really challenges the ab muscles,
    which means you must take into account the career of one’s lower back.

    It does not matters the way the child was created whether normal birth or operated.
    Research found that for every hour you’re working out your system can make $10,000 price of
    beneficial chemicals that it needs.

  3. Antioxidants – These promote the fitness of
    our cells, may help prevent cancer which enable it to slow the aging process.
    Recent surveys demonstrate that the volume of people
    deriving their required body nutrients away from these various
    food bars is increasing rapidly. Another food choice you could select, acquire the best choices to keep toddler healthy is baby
    food.

Leave a Reply

Your email address will not be published. Required fields are marked *