SQL 2000 Enterprise manager "Cannot open user default database"

We have been running a SQL 2000 SP4 server for the past 4 years without any problems. SQL is running on it's own hardware on top of Windows Server 2003.

Suddenly I noticed that when entering Enterprise Manager and trying to open the local server that I would receive an authentication error. I can still connect to other SQL servers on the network and manage them successfully.

I then removed the local server from Enterprise Manager and tried to add it again, at which point I got the following error "(LOCAL) - Cannot open user default database. Login failed"
(We are using Windows authentication)

I am also unable to manage that specific server from another workstation.

All databases on the server are up and running, but I am unable to manage them at all.

I can't find anything specific to this in the event logs...
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

log on with a SA account and see which is the default database for the failed user. Then make sure they have the correct permissions for that database

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dexaniAuthor Commented:
Unfortunately we do not have the sa password for this server since we have always used Windows auth, is there some way of resetting it ?
is there a windows account that is a member of the sysadmin role?
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

dexaniAuthor Commented:
Not sure, what I did notice is that if I login ad local administrator, not the domain administrator I am able to use Enterprise manager...
dexaniAuthor Commented:
The previous comment should have read :
Not sure, what I did notice is that if I login as local administrator, and not the domain administrator I am able to use Enterprise manager...
ok when in enterprise manager, go to security under the server name, find the user who is having problems and check what their default db is. Now under user mapping check to make sure they are a member of at least "public" in that db. Either do that or change their default db to one where they do have permissions.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.