Friday, March 9, 2012

Help! Cant Connect - db offline

OK, so I took one of my db's offline for maintenance, then realized that I wanted to be logged in as another user, so I logged out.

Now SS won't let me log in, because every time I try to connect I get:

A connection could not be established...
Reason: Cannot open user default database. Login failed..

The db I took offline is the default db for every single privileged account on the server. Why do I feel like I just locked my keys in the car?

Any help would be greatly appreciated! TIA!Never mind, I figured it out. Logged in via ISQL, and issued:

exec sp_defaultdb '<my sa account>', '<a db known to be online>'

ISQL let me log in, and simply used 'master' instead of the default db that was offline. What reason in the world could there be for EM behaving differently?|||In the future you should set master to be the default database for all users.|||Why? I only do this for the admin group.|||In the future you should set master to be the default database for all users.
Why in the world would you want to do that for application users ?|||Enterprise manager is a good tool to start learning about SQL Server with, but as you get to more and more advanced things, it helps more to use Query Analyzer, or if you are super-hard-core isql or osql. However, in this case, I am not certain how you did get around this. This may take a bit of experimentation for me to figure out. I had thought that any login is sent to the default database, even if just fr a moment, when you have the -d switch set in isql, or a database name set in your connection string. I could very well be wrong.

But essentially, yes. If you drop the database that all of the admin users have as their default database, then you have effectively "locked the keys in the server".|||In the future you should set master to be the default database for all users.

He's just drunk...

and I'm jealous...

No comments:

Post a Comment