"Cannot open user default database. Login failed." after installing SQL Server Management Studio Express

Question

I have a database in a local file that is used by a program. The program has limited functionality and I needed to run some quick queries. I installed SQL Server Management Studio Express 2005 (SSMSE), connected to the SQL Server instance, attached the database file, and ran the queries. Now the original program will no longer connect to the database. I receive the error:

Cannot open user default database. Login failed. Login failed for user 'MyComputer\MyUserName'.

I've gone back into SSMSE and tried to set the default database. I've opened up Security, Logins, BUILTIN\Administrators and BUILTIN\Users. Under General, I have set the default database to the program's database. Under User Mappings, I made sure the database is ticked and that db_datareader and db_datawriter are ticked.

The program uses the connection string:

Server=(local)\Instance; AttachDbFilename=C:\PathToDatabase\Database.mdf; Integrated Security=True; User Instance=True;

I know jack-all about database administration. What else am I missing?

Answer 1

First, try to isolate your problem:

1. Take a backup of the file! Some of the steps below can, apparently, in some circumstances cause the file to vanish.
2. Are you sure you are connecting to the same instance through Management Studio as the program is?
3. If possible, try to shut down the instance that you are not expecting to use.
4. Set the user's default database to master and try to make the program logon.
5. Try to login as the user through Management Studio - since you have integrated security, you should open Management Studio as the program's user.
6. Are you using "User instances" - perhaps without knowing it? If so, this may be helpful: http://blogs.msdn.com/b/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

I haven't worked much with files being attached in the way your program does - but you write that you attached the DB in the Management Studio as well. Have you tried detaching it there before running your program? Perhaps you are seeing the Management Studio and your program competing for exclusive access to the MDF-file?

EDIT: I added point 6 above - this is new in my own list of TODOs when troubleshooting this type of Login failed. But it does sound a lot like what you're experiencing.

EDIT2: In the first edit, new item was added to the list. So the numbers in the comments doesn't correspond with the numbers in the answer.