31

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?

Improve this question

9 Answers 9

Reset to default
38

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/

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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

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).

Improve this answer

Comments

9

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.

Improve this answer

5 Comments

All users are now have the default database set to "master". Using SSMSE, I connected without issue to "(local)\Instance", exactly the same as the program, using integrated security. The program still gives the same error message. I've even created a login specifically for me and still have no love.
I tried detatching the database in SSMSE. It turns out "Detatch" is synonymous with "Delete" and my database vanished into the binary ether! Thankfully I had a current backup. Additionally, the backup wasn't "corrupted" by SSMSE and worked with the application.
Point 5 describes and solves my problem exactly for the future. Thank you so much for your help! It's a shame I can't give you bonus rep for that.
Ouch. Glad point 5 helped - sorry to hear that detaching actually killed the file. Will update the answer in a moment.
Note to future readers: point 5 has been changed to point 6. "User Instances" was the source of the problem.
6

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.

Improve this answer

Comments

3

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/

Improve this answer

Comments

2
  1. Select Authentication as Windows Authentication when login.
  2. Then go to Security>Logins and select the created login.
  3. Right-click on properties and go to the default database settings.
  4. Select the required database and select OK.
Improve this answer

Comments

1

I've also had this same problem, it turned out that I was trying to access the built in membership classes (in a view), and that .Net was trying to create the database in the App_Data folder:

@Membership.GetUser().ProviderUserKey

This will trigger the system to try and create a database based in the built in membership system, which may not be the way your system is setup.

Improve this answer

Comments

1

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.

Improve this answer

Comments

1

In my case I had to set "connect to any database" right path:

On your instance, go to Security , then to Logins.

Right Click on there, you will see properties and you should click on Securables.

There it give possibility to connect to any database.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.