I am no longer able to access an instance on our server. I get an error message that says "Cannot open user default database". I do not have the password for the sa account (I'd have to contact my network administrator).
How do I restore access to my instance and connect to a different database, or how do I change the default database to master? The login box won't recognize any other database - it will spit out the same error and not show the other databases in that instance.
I cannot even see master.
Ok, here's how the problem started in the first place: I wanted to change the filename of the database log file from ENVIS_1.ldf to ENVIS_log.ldf. While connected to the instance (GIS\MBX), I detached the database ENVIS from the instance. And yes,
this database was the default database. Probably a bad idea.
First of all, yes, I do have a previous day full backup of my database (including backups of the master, msdb, and model database), with no changes. So I can restore the database on another instance, if necessary.
How do I get out of this dilema? Please note that I am pretty new to SQL Server, so I require a bit of hand-holding along the way.
The solution was to type the word master in the Connect to database text box under the
Connection Properties tab in the Connect to Server dialog box. Don't select or browse for a database (you won't see any!). Don't leave it at <default> either, because that just tells SQL Server to use the default database
you specified - which is the one you can't connect to. Entering master will reset the default database to the master system database. After pressing Connect, I was able to access the instance, and then reattach the missing database that was my default.
I tried using the sqlcmd solutions posted elsewhere to reset the default database, but they did not work.
Please note that my solution worked for SQL Server 2008 Standard. I'm not sure if the solution will work for SQL Server Express, but give it a try.
Marked as answer byrupertslandThursday, January 13, 2011 9:06 PM