October 1, 2007 at 9:26 am
I am the administrator of a SQL Server 2005 on Vista Ultimate x64.
I detached a database which was incidentally my default one.
Now I am in this vicious circle: I cannot login and thus I cannot reconnect the database and thus...
Is there a way out please? :w00t:
October 1, 2007 at 9:40 am
What I did to get myself out of this one is this :
Open up access (or anything where you feel good about coding a bit). Create a connection object. Connect to the server (specify the db in the connection string).
Then run this : sp_defaultdb [ @loginame = ] 'login', [ @defdb = ] 'database'
I would strongly advise you set it to master and then never changing it again... but that part is up to you :P.
October 1, 2007 at 9:57 am
On the login dialog of management studio, there's options button. Click that and you'll get a second tab that includes a database dropdown. Type master in there and you should be able to connect.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2007 at 10:02 am
Does that work in QA as well (I don't have it installed here... I can't check for myself, and I'm guessing it can help someone else as well)?
October 1, 2007 at 10:11 am
Ninja,
Many thanks for your prompt reply.
I'l give it a try from Visual Studio.
Thanks again
Dinu
October 1, 2007 at 10:17 am
Gail,
Many thanks.
I managed to login, but then if I try any command (New Database, Attach etc) I get that dreadful message: cannot open the default DB.
Dinu
October 1, 2007 at 10:36 am
Dinu E Anastasiu (10/1/2007)
Gail,Many thanks.
I managed to login, but then if I try any command (New Database, Attach etc) I get that dreadful message: cannot open the default DB.
Dinu
Thanks for the feedback... unless Gila has other ideas, looks like you're back to square 1. Let us know how it goes.
October 1, 2007 at 10:39 am
Are you trying from object explorer? That always tries to create a new connection for every action. You'll have to fix the problem in script.
Don't bother with object explorer. Get a new query window (using the options to set the default db as you log in), then run the sp_defaultdb code that Ninja gave and set your default db to master. You should have no problems after that. Or you can just write a script to reattach the DB.
No idea about QA. Don't use it any more.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2007 at 10:41 am
Ninja's,
I created a new SQL Project in VS 2005
Using Server Explorer I connected to another DB on the same server. I am able to see all databases on that server and their contents.
I started writing a SQL Query.
First I tried only with "use master go"
Reply: "Cannot open database x requested by the login. The login failed. Login failed for user 'x\z' " (x is the default DB).
Still that vicious circle I'm afraid.:w00t:
October 1, 2007 at 10:45 am
My access trick always worked... maybe the fact that no GUI interferes with the code sent the server helps a lot in that case. Something to be said about being too user friendly ;).
October 1, 2007 at 10:56 am
Problem solved 😀
1. As already reported I managed to login to the Master DB using the Options button as suggested by Gail.
2. In Master I created a query where I executed sp_defaultdb as suggested by Ninja's
3. Magic!
Many thanks to both of you,
Dinu
P.S. Would it not be normal for the Studio to warn before it detaches a default DB? :w00t:
October 1, 2007 at 11:02 am
No offense, but you can create a sp, why can't you use that statement to directly exec the sp_defaultdb procedure and skip the first step?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply