Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL SERVER 2000 cannot open user default database

Posted on 2011-04-22
12
Medium Priority
?
562 Views
Last Modified: 2012-05-11
I detached 2 databases in sql 2000 and one of them must be the default database because now I get  Reason: can not open user default database when opening enterprise manager.
I have tried to use isql and changed the default database for sa to master but I am still unable to login - perhaps I need to change the default database for all users - I am logging in as windows administrator and and am not prompted for id/password. I beleive they use windows authentication. Trying to help a friend but getting frustrating - thanks in advance.
0
Comment
Question by:Redshaw
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 9

Expert Comment

by:radcaesar
ID: 35447496
Have you granted access using sp_addrolemember and sp_grantdbaccess

http://support.microsoft.com/kb/196076

0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35447568
see attached screen Change default database
0
 
LVL 9

Expert Comment

by:radcaesar
ID: 35447623
Can you please explain the problem in screen snap clearly?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Redshaw
ID: 35447633
Patel, thanks - I am assuming I am logged in as sa - when I go into enterprise manager I am logged onto the server as domain administrator and am not prompted for a password.. When I go to tree and try to open sql in tree I get the can't login to default. and this was working fine prior to my detaching the databases - (getting an offline backup). Would I need to do the granting in this case?
thanks
0
 

Author Comment

by:Redshaw
ID: 35447650
sorry radcaesar - that last comment was for you.. I don't get the screen shot either - unfortunately I am working on an sql2000 system - and I don't think I can get there..
0
 

Author Comment

by:Redshaw
ID: 35447712
Is there an alternate way for me to re-attach the 2 databases that I detached without using enterprise manager. cmd line for example (once these are attached I think I will be able to open  enterprise manager).
thanks
0
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 2000 total points
ID: 35448878
0
 

Author Comment

by:Redshaw
ID: 35449274
sorry, just got the DB's back online.. here's what I did.. I changed the default database to  Master for the sa user, then I edited the connection properties by right clicking on sql in the tree on the left and changingit from windows logon to sql logon then I logged in as sa and I could see the databases so enterprise manager opened after that change -  which allowed me to attach the db's back - then users were able to access.. OP Zahrin had a good response so I will award him the points..
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35451310
>>OP Zahrin had a good response so I will award him the points.. <<
I am objecting to allow the author to award points to OP_Zaharin
0
 

Author Closing Comment

by:Redshaw
ID: 35452929
Thanks
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35452953
thank you redshaw :)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question