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