Geeks With Blogs
Chaz Blogs It's all about the eXperience!

Wow weeee...  did I ever just make a blunder!  I wanted to do some performance tuning on a SQL Express DB.  So I detached the DB from SQL Express and Attached it to SQL Server.  I did my tuning (created a bunch of recomended indexes) and was about to reverse the process.  When I went to re-attach the DB to SQL Express boom...  “Cannot Open User Default Database (error 4606).  DOH!  My bad.  I didn't know the sa password and both my one sql server authentication account and my windows integrated authentication account threw the error message from SQL Express.  Luckily I found help on the MSDN Forum.  But I had to search just the right way to find it.

So without repeating everything in the forum here are the quick steps I followed using SQL Server Management Studio to get in.

1. Start SQL Express server if it isn't already.

2. On the Login click the “Options” button.

3. In the “Connect To Database” drop down box type in “master” without the quotes. 
(This is the misleading part of the UI.  I didn't realize I could just type in a db to connect to I thouht I had to select from the list.  Bad UI design!)

4. Now you should be able to login succesfully.

5. Run this script;
ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master
GO

Assuming your account belongs to the Administrators group, you should be home free...  You can reattach the needed DB or do whatever else you have to do.

--chaz

Posted on Friday, August 25, 2006 9:55 AM Microsoft | Back to top


Comments on this post: Cannot Open User Default Database (error 4064)

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thank you,although the query doesn't work with me but anyways I can connect now:)
Left by Can on Sep 03, 2006 5:13 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thank you so much. Worked first time !!!
Left by Wayne Jagger on Nov 16, 2006 2:34 PM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Hi,
Thanks a lot.
Left by Revathi on Dec 15, 2006 1:23 PM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thanks a lot!!!It did work for me.
Left by Kashish on Mar 28, 2007 8:30 PM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Great!!!!! Thanks a loooot!!!
Left by Fachmann on Jul 18, 2007 8:24 PM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Hey Fachmann,

Did you substitute the string [BUILTIN\Administrators]with something else or enter as is?

Please advise as I am having similar problem.

Thanks
Left by BillV on Jul 19, 2007 8:27 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Folks,

If you are using a domain account and it doesn't happen to be in the administrators group substitute the above for this:

[Domain\youraccount] That is what I had to do.
Left by cj on Aug 07, 2007 2:26 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
you r a *, thnks
Left by james on Sep 11, 2007 8:51 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
THANK you ... it's really work :).
Left by Florian on Sep 14, 2007 7:09 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thanks so much. I had a panic attack when I dropped an old database and couldn't get back in. The query worked perfectly, of course because I had the specified login setup. For those using a different login, obviously you need to specify your own login in place of [BULITIN\Administrators]. Thanks again for this blog post.
Left by William on Sep 18, 2007 1:50 PM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thanks for this. It saved my butt as I didn't realize you could simply type the database into the 'default database' combo box.

Keep up the good work!
Left by Adam Gurno on Oct 11, 2007 11:19 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thanks a lot! It worked successfully.
Left by Rathnavathi on Feb 20, 2008 11:50 PM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Didn't fully work for me, because the sa user still did not get updated for the default master DB. So once I had dropped the login with the script, I conected back into the database using the Windows Authentication login and used the Admin login for the server, and was then abble to set a default database for sa. Ok after that.
Left by Ewan on Mar 03, 2008 12:13 PM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thank you, whoever you are, for taking the time to post the solution. I thought I would have to leave town.
Left by Camille Trapp on Mar 26, 2008 10:30 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
found your article on goolge. you saved my life!
Left by Fernando on Apr 08, 2008 2:44 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thanks a lot. You haved my time. Great work. Thanks agian...
Left by Jigar on Apr 22, 2008 8:53 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thanx! Works!
Left by Jeroen on May 20, 2008 3:04 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
If all of the above doesnt work try this:

ALTER LOGIN [YOURMACHINENAME\Yourname] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
Left by Fher on May 30, 2008 6:41 PM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thanks a lot! You saved my life! :o)
Left by Natalie on Jun 05, 2008 11:37 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
thanks and many thanks!!!
Left by mm on Jul 21, 2008 11:34 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Superb! I was just about to do a reinstall and then I found this page! Thanks!
Left by hm on Aug 07, 2008 7:52 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
I saw my life flash before my eyes till I read this, THANK YOU SO SO SO MUCH!!
Left by Laza999 on Aug 19, 2008 11:47 PM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thanks a ton for this. This was the quick turnaround. Saved lot of time
Left by Prashant Raut on Sep 11, 2008 8:46 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Vraiment je vous remercie pour la solution!!
Left by sabrina on Sep 20, 2008 7:26 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
You save me a lot of time!!! Great job!!!
Left by LGuimaraes on Sep 23, 2008 9:08 PM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
wow...the best..it works...thnk u so much..Godbless!Continue your good deeds!hehehe...
Left by John Paul on Sep 24, 2008 11:29 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
waahh..i am now connected to the sql server 2005 but why is it that i can't attach a database?is there any good someone that can help me???thanx in advance...
Left by John Paul on Sep 24, 2008 11:41 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thanks, you saved my bacon!!!
Left by Chris on Sep 30, 2008 10:48 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thanks

Worked like a charm!
Left by VWT on Oct 23, 2008 7:42 PM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thank you very much for posting this! It saved me a lot of hours and headaches.

Thanks!
Left by simz on Oct 28, 2008 4:42 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Ooooohhhhh Thanks you solved my 2 hours problem just in half of minute. Thanks again thanks.
Left by Ghulam Haider on Nov 14, 2008 2:17 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thanks! It is works!
Left by TYev on Nov 18, 2008 5:25 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
thanks a lot. worked better than what microsoft put on their site.
Left by doris on Dec 16, 2008 5:13 PM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Eres lo maximo
thanks a lot.
Left by JoE on Mar 04, 2009 6:59 PM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thanks a lot, this helped me big time!
Left by Per E on Apr 09, 2009 3:45 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thanks, this worked for me but first I had to run the sql server instance in single user mode by adding -m to the startup options.
Left by Kevin on May 08, 2009 10:20 PM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thanks for the reminder about the Options button on the login dialog. Another way to go, for those who eschew GUI tools, is to use SQLCMD with the -d option to connect to a specific database. From a command prompt: sqlcmd -S <server> -d master ... then at the SQLCMD prompt, type your ALTER LOGIN command, with GO on a separate line. If your Windows account isn't in the BUILTIN\Administrators group, use the -U option to specify another login.
Left by Na3than on Jun 18, 2009 2:40 PM

# Super!
Requesting Gravatar...
Schade, dass ich den Blogg erst heute gefunden habe - weiter so.
Left by Christina Pronegg on Aug 06, 2009 12:29 PM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thanks a lot ...it's working...
Use below steps:

1. Start SQL Express server if it isn't already.

2. On the Login click the “Options” button.

3. In the “Connect To Database” drop down box type in “master” without the quotes.
(This is the misleading part of the UI. I didn't realize I could just type in a db to connect to I thought I had to select from the list. Bad UI design!)

4. Now you should be able to login succesfully.

5. Run this script;
ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master
GO


Left by Ramesh on Sep 01, 2009 5:16 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Very-very thanks, it worked for me.
Left by arun on Dec 08, 2009 6:17 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thank you so much; I worked days around this problems, but to no avail.

It is just amazing; and it is simple. There are so many 'experts' on line who just make you walk in circles. You are a real saviour.
Left by Kumar on Mar 03, 2010 6:56 PM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thanks a lot dude.. but does anyone know why this problem comes.. ?
Left by Murli Krishna on Aug 20, 2010 4:40 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Awesome. This worked!
Left by Surj on May 31, 2011 6:03 PM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Very helpful. My default database was deleted while fixing profile issue. It saved me time to reinstall SQL server and Analysis Services (I thought I had to if I could not login to the server anymore).
Left by Helenees on Nov 22, 2011 10:37 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Thanks.. It worked fine...
Left by Dilip on Jun 26, 2012 4:47 AM

# re: Cannot Open User Default Database (error 4064)
Requesting Gravatar...
Awesome. right the first time itself. thanks a lot
Left by R Mishra on Sep 18, 2012 7:33 AM

Your comment:
 (will show your gravatar)


Copyright © Chip Lemmon | Powered by: GeeksWithBlogs.net