?
Solved

Cannot open user default database. Login failed.

Posted on 2006-06-05
21
Medium Priority
?
434 Views
Last Modified: 2011-10-03
I keep getting the title error message "Cannot open user default database. Login failed." I can connect to the database through enterprise manager, but cannot connect through a web applicant that uses the database as a back-end. Everything has worked perfectly for the past couple months. All of a sudden, I get this. Any ideas?
0
Comment
Question by:Feyo
  • 9
  • 7
  • 2
  • +3
21 Comments
 
LVL 15

Expert Comment

by:mcmonap
ID: 16836492
Hi Feyo,

you have deleted the database that your account tries to default to. you can change this by going into your server in enterprise manager, selecting security, selecting logins, right click the user account in question and select properties, in the defaults section at the bottom ensure a valid db is selected.
0
 
LVL 4

Author Comment

by:Feyo
ID: 16836561
I haven't done anything. Not a thing. I checked the setting you suggested, but everything is correct.
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 16836575
Perhaps someone else has changed something? also check what rights the user has in the database - make sure they have not been denied access to the default database.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 4

Author Comment

by:Feyo
ID: 16836589
I'm the only one who has Access, the application is still in development.
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 16836639
try this from a command prompt:
osql -E -d master
or
osql -U username -P password -d master

If this works try changing the setting I mentioned above to the master database and try QA again - if this works try changing issuing a USE databasename in QA, if this works try changing the default database back in EM
0
 
LVL 4

Author Comment

by:Feyo
ID: 16836818
I can connect via Query Analyzer and perform queries, but the same username/password cannot connect through the application. Very strange.

osql -U username -P password -d master works
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 16836926
Hi Feyo,

Please try what I suggested above, I suspect these is something worng with what SQl thinks is your default database, doing those steps should fix it.
0
 
LVL 4

Author Comment

by:Feyo
ID: 16837048
Okay. The steps are:

1) osql -U username -P password -d master works on the DOS command line
2) ????
3) ???

I got a bit confused
0
 
LVL 15

Accepted Solution

by:
mcmonap earned 2000 total points
ID: 16837138
>>>>>>>>>>>>>
change this by going into your server in enterprise manager, selecting security, selecting logins, right click the user account in question and select properties, in the defaults section at the bottom
>>>>>>>>>>>>>

2) try changing the setting I mentioned above to the master database and try QA again
3) then try issuing a USE databasename in QA
4) then try changing the default database back to the original EM

post back if any step fails
0
 

Expert Comment

by:apparition0
ID: 16837287
In addition to going to
Enterprize Manager -> server -> Security -> Logins -> username
double clicking and making sure a valid DB is selected.  (as stated in the first post)

Also check on the "Database Access" tab, of the user, to make sure the
same database is checked.  IF it is not, it will generate this error, I believe.

If you want to allow this user to read select "db_datareader", and write select "db_datawriter".





If this doesn't work, I have seen the error where a database is restored, and it appears the users
have been created in the DB, but the DB username (even if spelled the same) is not thoe one SQL
recognizes in the Security tab.  The only way I know to correct this is to delete the name from both the
Securtiy tab (for the whole SQL server) _AND_
Enterproze Manager -> serveranme -> Database -> your_database_name -> users -> that username.
0
 

Expert Comment

by:apparition0
ID: 16837294
sorry,
last step is to recreate the userid, with all the right permissions.
0
 
LVL 4

Author Comment

by:Feyo
ID: 16838462
Okay. I'm a bit confused on what you want me to do. Can you provide a bit more detail on:

2) try changing the setting I mentioned above to the master database and try QA again - where in the master database, what do you mean by master database? The one that is created by sql server by default?
3) then try issuing a USE databasename in QA - You mean try invoking a use databasename on the database that I'm having a problem logging into with the user who is having a problem?
4) then try changing the default database back to the original EM - You me put the default database back to what it originally was before I started having problems?

Why would I not have problems connecting to this database in enterprise manager, but do have problems connecting via my application? This makes no sense to me. Will these steps fix this problem?

Thanks
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16838991
Have you restored this database from another server? If so, you will need to delete the user from the database, then reassign permissions to the user.
0
 
LVL 4

Author Comment

by:Feyo
ID: 16838995
That's the thing, I haven't done anything to the database. I even dropping the login and reassigning permissions, but I'm still getting the same error message.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16840385
first make sure that your web application is using the same user what you are using in enterprise manager. try checking your connection string and security directory tab in the IIS. what is the user specified there.
0
 
LVL 4

Author Comment

by:Feyo
ID: 16841316
imran_fast - I don't think it has anything to do with IIS because it's just my local instance of sql server that causes that error. I can connect to any other database with no problem. Checked specified users about 300 times. They are appear correct.
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 16841531
Feyo,

2) Not sure what is not clear here:
>>>>>>>>>>>>>>>>
change this by going into your server in enterprise manager, selecting security, selecting logins, right click the user account in question and select properties, in the defaults section at the bottom select master database.
>>>>>>>>>>>>>>>>

3) yes

4) yes

5) the whole point is to try it and see.  Or we can discuss it. endlessly. forever. until the cows come home......
0
 
LVL 4

Author Comment

by:Feyo
ID: 16841551
mcmonap - Sorry, I don't claim to be a sql server expert and what may be 2nd nature to you, is not to me. I simply did not understand what you were suggesting. That said, I cannot get past step 3.
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 16842021
I realise that, but its taken 20 hours to just try the steps! :)

Do you get an error message at step 3?  If so what?
Also have you checked, as apparition0 said above, the database access tab? on the login properties?  You need to ensure that the the account has a check mark against your database.
0
 
LVL 8

Expert Comment

by:Julianva
ID: 16842079
did you check the code in the application, its probably changed so the login credentials does not have access to that database.

logins should be in the security logins tab
go to the login that is specified in the webapp in sql enterprise manager then right click choose properties option then choose database access tab - check to see if that login has access to the database in question - will be a tick in the box next to it.

then go to the database in question using enterprise manager go to users check if the login name is a user for that database.


0
 
LVL 4

Author Comment

by:Feyo
ID: 16842456
I followed the steps in this article:

http://support.microsoft.com/?kbid=307864

Then, followed the steps outlined by mcmonap. Everything seems to be working now. Part of my confusion stemmed from the fact that I have this same problem on our dev server, which is linked to my local instance. My application was calling a stored procedure pulling data from the linked dev server on my local instance and that is why I was getting this error. I thought it was my local instance the whole time, but it wasn't. I'll have to talk to the dev sys admin to see what's going on.

Thank you all for your replies and sorry for the frustration!!!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

829 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