User Default Database 1

[TeriCoombes]

I do not have a lot of SQL experience so I hope this makes sense to someone.

SQL Server 2000 is loaded onto our server. I have been working with it through my computer on the enterprise manager. Everything was fine until suddenly today I received an error:

A connection could not be established to SERVER.
Reason: Cannot open user default database. Login failed..
Please verify SQL Server is running and check your SQL Server registration properties (by right-clicking on the SERVER node) and try again.

I checked and saw that SQL is running. I check the registration properties and everything seems fine, except the server name is shaded out. So I try creating a new SQL regristration. When I do, our server is not listed on the Available servers list.

What do I do now? Please help.

 

[SQLBill]

First, are you using Enterprise Manager on the same computer as SQL Server is loaded? Or is it on a different computer?

If it's on a different computer, are you sure you still have a connection to that computer?

How are you trying to log in? Windows Authentication or SQL Server Authentication?

What was your default database?

Can you open Query Analyzer (Start>Programs>Microsoft SQL Server>Query Analyzer) and log into SQL Server?

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

Sounds like your default database was deleted. Have someone log in with admin rights and change your default database. If you have admin rights on the SQL Server use osql to login and use the -d switch to specify another database to connect to. Then modify your account and change the default database.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[TeriCoombes]

SQLBill:
(1)I am using Enterprise Manager on my computer while SQL Server is on our server. (2)I am still connected to the server because I can still use the ADP that is on my computer. (3)We have set up the databases and connections for Windows authentication. (4)I don't know what my default database was...which I think is the main reason I am having this problem. Do those answers help?

mrdenny:
How do I find out about how to use osql? The help doesn't seem to have anything about it.

Thanks to you both!

 

[SQLBill]

I would check with your DBA to find out:

1. do you still have a login for the SQL Server?
- if so, does it still use Windows Authentication

2. if you do have a login, what's your default database?
- does your default database still exist?
- do you still have access to the default database?

If you are the DBA, log in to the server and open Enterprise Manager from there. Can you log in? Can you see the databases?

Another thing to try, can you open Query Analyzer and run a query from your computer? If you can open it, run this:
USE Master
SELECT GETDATE()

If you get a result, you know you can access the Master database. Then try to access your database.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

By the way, if you or someone else can run a command on the database....run this:

USE Master
EXEC sp_defaultdb '<yourlogin>', 'Master'

That will change your default database to the Master database. Then try to connect.

-SQLBill

Posting advice: FAQ481-4875

 

[TeriCoombes]

Thanks. I am going to give it a try.

 

[mrdenny]

osql is a command line app that comes with SQL server. Open a command prompt and type "osql /?". This will give you all the options. I'd suggust having your DBA check your account.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[SQLBill]

Okay, I got a star. I guess my solution helped you. Did it? Or did I guide you to the right solution?

-SQLBill

Posting advice: FAQ481-4875