Cannot open user default database. User login failed.

Hi,

I'm sorry if this is simple, I'm no DBA but have been tasked with solving this problem...

We have a website that connects via ODBC to SQL Server (2k sp1) and at the moment I am getting back about every other time:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.

So this is NOT happening every single time. Now I have seen Microsoft KB - 307864 and I can see that none of the databases are marked as suspect, the database I am trying to connect to does exist and is attached and, I have run the command to switch the database to multi-user mode.

The probable cause of this problem is that a while ago we had a hard-drive failure and I was forced to reattach some old datafiles (mdf,ldf) as the database. This seemed ok and I can view data etc in enterprise manager no problem.

I have checked for orphaned users and the user I am logging in with from the webpage is not listed.

So does anyone have a clue as to why this is happening, and more frustratingly for me, why is it only happening some of the time.

Thanks for your help, appreciated.

James.Edit your odbc connection and check the default database drop down box...it's probably set to master...|||Brett - Thanks for posting.

Have checked the ODBC settings and the database is not set to master as default .|||Originally posted by JDMoore
Brett - Thanks for posting.

Have checked the ODBC settings and the database is not set to master as default .

What ODBC says if you are trying to test data source under this account?
If everything is fine with ODBC - check your application...|||ODBC says that the connection is fine.

The application has not been changed and has worked fine so I'm virtually certainly it's nothing to do with the program code. The code in question is simply opening a connection to the database.

As I say the error is sporadic presumably an ODBC connection string can either be right or wrong?|||Remotely, but possible, that the default database is in single user mode, and if you login successfully, it means that you're the first one to show up, while when you get an error, then someone's already there (that's to attempt to explain the sporadic nature of the error.) But to be proactive, open EM and go to that database (make sure to register the server with sysadmin privileges.) Check the Options tab of the properties to ensure that the database is not in single user mode. Also, go to Security folder, then to Logins and open properties for the account that the app uses to login and set the default database to master. This is to prevent the situation when a different database was renamed to the one that is specified now as default. And finally, speaking about renamed databases, can you check if it happened recently?|||You might also want to check the connection type in the ODBC. There are times when it doesn't matter whether your using Pipes or Ip but it will test just fine then throw sporadic error messages when actually using the application.

I would try going into the client config button (2nd screen in the ODBC) and if it's set to Pipes then make it IP (port 1433 is default but may have to be changed depending on network) or vice versa.|||Run sql profiler to see what logins are attempting to connect - I am curious to see if the problem is that the iuser account is failing to connect ...|||Thanks for all the suggestions. I am not in until Monday now but when I get in, I will try everything and then get back to you all.

Thanks for posting.|||As always it turned out to be pretty simple...

As I looked further through the code it turned out that the program was looking at this database and then looking at a database on a different server.

When I went to the other server it turned out that THIS database was in single-user mode and hence the sporadic nature of the problem was explained.

Thanks very much for everyone's contribution, probably wouldn't have stumbled over this for ages without your suggestions.

James.