Cannot open user default database. Login failed.

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?
LVL 4
FeyoAsked:
Who is Participating?
 
mcmonapConnect With a Mentor Commented:
>>>>>>>>>>>>>
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
 
mcmonapCommented:
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
 
FeyoAuthor Commented:
I haven't done anything. Not a thing. I checked the setting you suggested, but everything is correct.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
mcmonapCommented:
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
 
FeyoAuthor Commented:
I'm the only one who has Access, the application is still in development.
0
 
mcmonapCommented:
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
 
FeyoAuthor Commented:
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
 
mcmonapCommented:
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
 
FeyoAuthor Commented:
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
 
apparition0Commented:
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
 
apparition0Commented:
sorry,
last step is to recreate the userid, with all the right permissions.
0
 
FeyoAuthor Commented:
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
 
MikeWalshCommented:
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
 
FeyoAuthor Commented:
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
 
imran_fastCommented:
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
 
FeyoAuthor Commented:
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
 
mcmonapCommented:
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
 
FeyoAuthor Commented:
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
 
mcmonapCommented:
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
 
JulianvaCommented:
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
 
FeyoAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.