无法打开用户默认数据库。登录失败。安装SQL Server Management Studio Express后

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:

我在程序使用的本地文件中有一个数据库。程序的功能有限，我需要运行一些快速查询。我安装了SQL Server Management Studio Express 2005 (SSMSE)，连接到SQL Server实例，附加数据库文件，并运行查询。现在原始程序将不再连接到数据库。我收到的错误:

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

无法打开用户默认数据库。登录失败。用户“MyComputer my\用户名”登录失败。

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.

我回到SSMSE并尝试设置默认数据库。我已经开放了安全性、登录、内置的管理员和内置的\用户。通常，我将默认数据库设置为程序的数据库。在用户映射下，我确保对数据库进行了标记，并对db_datareader和db_datawriter进行了标记。

The program uses the connection string:

程序使用连接字符串:

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

服务器=(当地)\实例;AttachDbFilename = C:\ PathToDatabase \ Database.mdf;集成安全= True;用户实例= True;

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

我对数据库管理一窍不通。我还缺什么?

8 个解决方案

#1

First, try to isolate your problem:

首先，试着把你的问题隔离开来:

Take a backup of the file! Some of the steps below can, apparently, in some circumstances cause the file to vanish.
备份文件!显然，下面的一些步骤在某些情况下会导致文件消失。
Are you sure you are connecting to the same instance through Management Studio as the program is?
您确定通过Management Studio连接到与程序相同的实例吗?
If possible, try to shut down the instance that you are not expecting to use.
如果可能的话，尝试关闭您不希望使用的实例。
Set the user's default database to master and try to make the program logon.
将用户的默认数据库设置为master并尝试使程序登录。
Try to login as the user through Management Studio - since you have integrated security, you should open Management Studio as the program's user.
尝试登录作为用户通过管理工作室-既然你有集成的安全，你应该开放管理工作室作为程序的用户。
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
您是否正在使用“用户实例”——也许您并不知道它?如果是的话，这可能会有帮助:http://blogs.msdn.com/b/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instance - studics-inmanagements-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?

我没有像您的程序那样处理附加的文件——但是您写道，您也在Management Studio中附加了DB。在运行程序之前，您是否尝试过将其分离?也许您正在看到管理工作室和您的程序竞争对mdf文件的独占访问?

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.

编辑:我在上面添加了第6点——这在我自己的待办事项列表中是新的，当对这类登录失败进行故障诊断时。但听起来确实很像你正在经历的。

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.

在第一次编辑中，新项目被添加到列表中。注释中的数字与答案中的数字不一致。

#2

This may not be answering your question specifically but it will help other with similar issue

这可能不是回答你的问题，但它将帮助其他类似的问题

The problem is your user is defaulted to a database which is not accessable for any reason (can be renamed, removed, corrupted or ...) To solve the issue just follow the following instruction

问题是您的用户默认为一个数据库，该数据库由于任何原因无法访问(可以重命名、删除、损坏或…)要解决这个问题，请遵循以下说明

Try to login again on the login page there is other tabs select "Connection Properties".
尝试再次登录在登录页面有其他选项卡选择“连接属性”。
under the tab locate "Connect to database" and select an existing database you have access to like tempdb or master
在“连接到数据库”的选项卡下，选择一个您可以访问的现有数据库，比如tempdb或master。
Once you are connected to the SQL Server Instance execute the below TSQL to assign the login a new default database.

连接到SQL Server实例之后，执行下面的TSQL，为登录分配一个新的默认数据库。
```
Use master
GO

ALTER LOGIN [yourloginname] WITH DEFAULT_DATABASE = TempDB
GO
```

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

或者，连接后，通过UI将默认数据库名改为master

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

文章摘自:http://www.mytechmantra.com/learnsqlserver/fix -cannot-open-user-default-database- login - login - login - login - faiin -for user- sql - server - error/

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

我从备份中恢复了我的数据库。我知道我一直在使用一个特定的登录用户，所以我在SSMS中创建了这个用户。但是，在数据库中已经有一个用户以这个名字出现在备份中。

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:

因为我花了很多时间来修复这个问题，所以我不能很容易地删除DB下的用户。我删除了数据库并再次恢复。然后:

Delete the user under the Databases->[my database]->Users
删除数据库中的用户->[我的数据库]->用户。
Create the user again in Security->Logins (not under your DB, although that probably works too.
再次在安全性中创建用户——>登录(不是在您的DB下，尽管这可能也有效)。
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.
转到新创建的用户。选择properties。然后在用户映射下，告诉它使您的数据库默认。给它读和写访问。

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.

我有两个用户。一个是DB，一个是我创建的。删除带有DB的文件并创建您自己的文件。

#4

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:

我也遇到了同样的问题，原来我试图访问内置的成员类(在视图中)，而。net试图在App_Data文件夹中创建数据库:

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

这将触发系统尝试创建基于内置成员系统的数据库，这可能不是系统的设置方式。

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

我遇到过类似的问题，只需下载能够启动用户实例的SQL Express实用程序。SSEUtil是Visual Studio团队编写的用于帮助解决用户实例问题的工具，您可以在与该实用程序一起安装的read me文件中了解更多。http://www.microsoft.com/downloads/details.aspx?familyid=fa87e828 - 173 f - 472 - e - a85c ed01cf6b02&displaylang=en——27。

Hope this will help.

希望这将帮助。

#6

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

在我的例子中，我必须设置“连接到任何数据库”的正确路径:

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

在您的实例中，转到Security，然后登录。

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

右键点击这里，你会看到属性，你应该点击Securables。

There it give possibility to connect to any database.

在那里，它可以连接到任何数据库。

#7

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.

当我将默认db脱机时，这个问题就出现了。接下来我知道我无法登录。切换到Connection Properties选项卡并选择下拉菜单以更改要连接的数据库也失败了。

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

一旦我手动输入master作为我想要连接的db(在Connection Properties选项卡上)，它就会让我立即进入。

#8

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.

现在将连接数据库更改为服务器上的任何现有数据库，如master或msdb。

More Details

https://blog.sqlauthority.com/2008/11/04/sql服务器-修复错误- 4064不能开放-用户-默认数据库登录-失败-登录-失败- user/

#1