"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

This may not be answering your question specifically, but it may help others with similar issue caused by different problem

In my case the problem was my user is defaulted to a database which is not accessible for any reason (can be renamed, removed, corrupted or ...) To solve the issue just follow the following instruction

1. Try to login again on the login page there is other tabs select "Connection Properties".

2. under the tab locate "Connect to database" and select an existing database you have access to like tempdb or master

3. Once you are connected to the SQL Server Instance execute the below TSQL to assign the login a new default database.

   Use master
   GO
   
   ALTER LOGIN [yourloginname] WITH DEFAULT_DATABASE = TempDB
   GO
   

Alternatively once you connected change your default database name to master via UI

Article taken from : http://www.mytechmantra.com/LearnSQLServer/Fix-cannot-open-user-default-database-Login-failed-Login-failed-for-user-SQL-Server-Error/