cannot open user default database. logon failed

[comtec]

cannot open user default database. logon failed
This is what I get trying to logon using enterprise manager.
How do i get back into the SQL server?
I have tried everything( which isn't very much )
I accidentally shut down the main database to do some testing and obviously, something was setup incorrectly.
Please help.


this is what I get from quesry analyzer
server:msg 4064, level 16, state 1

Paul D
dontneednew.com

 

[Corran007]

Have your system admin take a look at your account. What exactly do you mean by shut down your main database?

Msdn has this to say

SYMPTOMS
Each user has a default database. When you connect to computer that is running SQL Server without specifying a login database, the default database is used. However, if the default database is not available at the time of the connection, you may not be able to connect. Instead, error message 4062 or 4064 displays. The text of the error messages is:

Cannot open user default database
CAUSE
The user default database is unavailable at the time of connection. It is possible that the database:
Is in suspect mode.
No longer exists.
Is in single user mode and the only available connection is already in use by someone or something else.
Has been detached.
WORKAROUND
If the user's default database no longer exists, or has been marked suspect, use the ISQL command line utility to change the user's default database to a database that is currently available for a connection.
At a command line prompt, type the following and then press ENTER: C:\>isql -E


At the isql prompt, type the following and then press ENTER: 1>sp_defaultdb 'user's_login', 'master'


At the second prompt, type the following and then press ENTER:2>go
The user can now connect normally.

That should help you out. Though why you cannot connect to it still should concern you.

 

[comtec]

You have hit the nail on the head
I can not login because I suspended the database.
It is the administrator login database.



when I type sp_defaultdb 'administrator', 'master'
I get
line 44 error the login 'administrator' does not exist
how can this be?

i am sure that it is a syntax, I am not fluent with sql command line or SP



Paul D
dontneednew.com

 

[Corran007]

sa is the default administrator for this. However if you have the builtin\administrators account still active, this may be how your trying to login. Change the administrator to builtin\adminstrators. see if that works. I would just login using sa personally. Its times like this when its good to have. Its also generally a good idea to leave sysadmins default database the master database.

 

[comtec]

Corran007
Problem Solved
Thanks, so very much appreciated

Paul D
dontneednew.com

 

[SQLBill]

What was the final solution? Always try to post the final solution or acknowledge whomever came up with it. That way, anyone else having the same issue will have an idea of what the solution might be.

-SQLBill

 

[comtec]

I logged in as sa as directed.
I then changed the default database back to master.
Everything worked correctly afterwards

Paul D
dontneednew.com