SQL SEM strange behavior

[Ted87]

Hello Everybody!

My problem is connected to the SQL Server Enterprise Managager. I worked with it for a day testing a database attaching/detaching it and after I closed the Manager the following problem occured.

The local database seems to be dissapeared. However, the applications through the network reach it because they can open it and modify it etc. When I open the EM and press the connect on the SQL server group it drops en error.

"The connection cannot be established to (LOCAL).
Reason: Cannot open user default database. Login failed.
Please verify SQL Server is running and check your SQL server registration properties"

The strangest thing that nothing seems to be changed at all there. We used Windows authentication for years now, the SQL server group is set at the Server group.

My aim would be to reach the live databases through this console again, but I do not know what should I do. I have been searching for the solution for days now.

Similar things happen if I try to open the Query Analyzer. Error message:
"[Microsoft][ODBC SQL Server Driver][SQL Server] Cannot open user default database. Login Failed".

What should I do? Make another server registration? What will happen with the current one?

Sorry for opening a new thread about my problem but I did not found another one similar to this. If it may happen, please ignore the upper text and link the forum topic here.

Thank you for your patience and understanding!
Ted

[BitBlt]

It has to do with how the login is set up. If you can connect to the SQL engine with some other SA login, go to the login properties of the login having problems and fix it there. Since you are using SQL2000 terms, I'll assume it's SQL2000?

Anyway, open up the server tree, navigate to Security/Logins, find the login, right-click and choose "Properties". At the bottom of the dialog where it says "Defaults", drop down the list of databases and assign it to one that exists. That should fix your problem.

[crishjeny]

have you started your server again using right click on database and click on start.
try another way...
write services.msc in "Run" and then start Workstation Automatically...it will start your database services

[Ted87]

Thank you for both answers!

I would start to answer with crishjeny:
Yes I had restarted the server many times, I checked the running services and the depenencies etc. before I wrote here. Unfortunately there seems everything is fine.

For BitBlt:
Yeah you were right. It is an SQL2000. What you write would be good if I was able to reach the Security/Login, but I can not at the moment. I attach a link about what I see from the SQL Enterprise Manager. You might better understand what happened.

http://kepfeltoltes.hu/view/110427/sqlmanager_www.kepfeltoltes.hu_.jpg

As you can see the green triangle in the middle the database seems to be working, however nothing can be reached.

Any idea for this?

[BitBlt]

Right-click on the server, select "Edit SQL Server Registration Properties". You should see how the registration is trying to connect. If Connection has "Use Windows Authentication" then change it to "Use SQL Server authentication" and log in as the "sa" account if you can, or some other SQL login with sysadmin rights. Are there other SQL logins you can use? Once you get in, use what I said above.

If you don't have another login to use, there are some other tricks to try, but it depends on what other logins are available on the machine. Did you remove the "Builtin/ADMINSTRATORS" role? And, are you local admin on your machine? If the answer to the first question is NO and the answer to the second is YES, then you can create a dummy NT login with local admin rights for your machine, then log into Windows with that and that login should inherit SQL sysadmin rights. You can then use the advice I gave above to repair your normal login. Just don't forget to clean up after yourself when you're done with the dummy login.

Good luck!

[Ted87]

Thank you very very much for your help BitBlt! It was an easy and fast way to solve the problem by your second advice :)

Have a nice day! Thank you once more!