"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?

Solution 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/

Solution 2

This problem manifested for me when I took my default db offline. Next thing I know I couldn't login. Switching to the Connection Properties tab and selecting the drop down to change the database I want to connect to also failed.

It let me in right away once I manually typed master as the db I wanted to connect to (on the Connection Properties tab).

Solution 3

I finally figured this out, and my situation is different than every other I've read about tonight.

I had restored my database from a backup. I knew that there was a particular login user that I had been using, so I created that user in SSMS. However, there was already a user by that name under the database that had come in with the backup.

Since I had screwed around so much trying to fix this, I wasn't able to delete the user under the DB easily. I deleted the database and restored again. Then:

1. Delete the user under the Databases->[my database]->Users
2. Create the user again in Security->Logins (not under your DB, although that probably works too.
3. Go to the newly created user. Select properties. Then under User Mappings, tell it to make your database the default. Give it read and write access.

Summary: I had two users. One that came with the DB, and one that I had created. Remove the one that came with the DB and create your own.

Solution 4

First click on Option>> Button of “Connect to Server” Prompt.

Now change the connect to database to any existing database on your server like master or msdb.

More Details

https://blog.sqlauthority.com/2008/11/04/sql-server-fix-error-4064-cannot-open-user-default-database-login-failed-login-failed-for-user/

Solution 5

I had a similar problem had to simply download SQL Express Utility that is capable of starting User Instances. SSEUtil is a tool written by the Visual Studio team to help troubleshoot User Instance issues, you can read more about it in the read me file that is installed with the utility. http://www.microsoft.com/downloads/details.aspx?FamilyID=fa87e828-173f-472e-a85c-27ed01cf6b02&DisplayLang=en.

Hope this will help.

View more solutions

Comments

[Masoud Sadeghi]

thanks kiarash, I also find this answer at : blog.sqlauthority.com/2008/11/04/…