在附加数据库时，访问被拒绝。

I am using SQL Server 2008 developer edition. I was trying to attach the AdventureWorks2008 database.

我正在使用SQL Server 2008 developer edition。我正在尝试连接AdventureWorks2008数据库。

When I tried to attach, I received an "access is denied" error. According to the event log, it came from the O/S:

当我试图附加时，我收到了一个“访问被拒绝”错误。根据事件日志，它来自O/S:

Open failed: Could not open file D:\ProjectData\AdventureWorks\AdventureWorksLT2008_Data.mdf for file number 0. OS error: 5(Access is denied.).

Open failed:无法打开文件D:\ProjectData\ AdventureWorksLT2008_Data。文件编号为0的mdf。操作系统错误:5(拒绝访问)。

I thought "NTFS problem", but System (and I) have modify access to both files.

我认为“NTFS问题”，但是系统(和我)修改了对这两个文件的访问。

I found that I can successfully attach the database if I log in as sa, but my user account won't work.

我发现如果我以sa登录，我可以成功地附加数据库，但是我的用户帐户无效。

I am a member of the local administrators group on my machine, and I am in the sysadmins role in SQL Server instance.

我是我的机器上的本地administrators组的成员，我在SQL Server实例中的sysadmins角色中。

Any idea why I had to be logged in as sa?

你知道为什么我必须以sa的身份登录吗?

30 个解决方案

#1

Run SQL Server Management Studio as an Administrator. (right click-> run as administrator) that took care of all the weirdness in my case.

作为管理员运行SQL Server Management Studio。(右击->作为管理员)处理我的案件中所有的古怪。

SQL SRV EXPRESS 2008 R2. Windows 7

SQL SRV EXPRESS 2008 R2。Windows 7

#2

Thank you for all of the comments. Some of you helped to lead me to the answer. Here's what I found:

谢谢你的所有评论。你们中的一些人帮助我找到了答案。以下是我的发现:

It was an NTFS permission problem, and not a SQL problem. Further, it looks kind of bug-like (and it's repeatable).

这是一个NTFS权限问题，而不是SQL问题。此外，它看起来有点像bug(而且是可重复的)。

The problem: The account that I was using had full control NTFS permissions to the mdf and ldf files. However, it had those permissions through group membership (the Local Administrators group had permissions, and my account is a member of local admins). (I verified the permissions)

问题:我使用的帐户完全控制了mdf和ldf文件的NTFS权限。但是，它通过组成员身份获得了这些权限(本地管理员组有权限，我的帐户是本地管理员的成员)。(我的权限验证)

If I try to do the attach, connect to SQL Server as me (where I am in the admins group), it fails with the NTFS problem.

如果我尝试连接到SQL Server(我在admins组中)，它就会失败于NTFS问题。

However, if I grant the same file permissions that the local admin group has directly to my Domain Account, then I can attach with no problems.

但是，如果我授予本地管理组直接到我的域帐户的相同的文件权限，那么我就可以附加任何问题。

(oh, and yes, I checked the local groups on this machine, and I verified that my domain account is indeed a member of the local admins group).

(噢，是的，我检查了这台机器上的本地组，我验证了我的域帐户确实是本地管理员组的成员)。

So, it looks as though the error occurs because some code (either in SQL Server or Management Studio) checks for the permissions that the user account holds, but it doesn't go so far as to check group permissions that the user account inherits.

因此，似乎出现了错误，因为一些代码(在SQL Server或Management Studio中)检查了用户帐户所持有的权限，但它并没有检查用户帐户所继承的组权限。

That sounds weird to me, but I can reproduce it over and over again, so I have concluded that it is the answer.

这听起来很奇怪，但我可以一遍又一遍的重复，所以我得出结论，这就是答案。

Update: I reported this as a bug: https://connect.microsoft.com/SQLServer/feedback/details/539703/access-denied-attaching-a-database-when-permissions-are-inherited

更新:我将其报告为一个bug: https://connect.microsoft.com/sqlserver/feedback/details/539703/access-database -when-permissions- - - - - - -继承。

#3

I'd like to add additional info to the answers that were posted.

我想给你的答案添加额外的信息。

Be careful when detaching the database because the windows user you are logged in as becomes the only user with permissions to the .mdf file! The original permissions the .mdf file had which included the user SQLServerMSSQLUser$<computer_name>$<instance_name> and the Administrators account get overwritten by whichever windows user you are logged in as (not sql server user). Boom, all permissions gone just like that. So do as others have said and right click your .mdf file and double check the permissions.

在分离数据库时要小心，因为您所登录的windows用户将成为唯一拥有.mdf文件权限的用户!.mdf文件的原始权限包括用户SQLServerMSSQLUser$ $ 和管理员帐户被您登录的任何windows用户所覆盖(不是sql server用户)。嘣，所有的权限都消失了。因此，正如其他人所说的那样，右键单击您的.mdf文件并再次检查权限。

I ran into this problem because I used SSMS to connect to the database (doesn't matter which sql server account) and detached the database. After doing that my windows user was the only one that had any permissions to the .mdf file. So later on when I tried to attach the db using the sa account, it threw the "access denied" error.

我遇到了这个问题，因为我使用ssm连接到数据库(不涉及哪个sql server帐户)，并分离数据库。在做了这些之后，我的windows用户是唯一一个对.mdf文件有任何权限的人。因此，当我尝试使用sa帐户连接数据库时，它抛出了“访问拒绝”错误。

To keep the original permissions in tact you should take the database offline, then detach, then attach in that order like so:

为了保持tact的原始权限，您应该将数据库脱机，然后分离，然后按照这样的顺序连接:

USE [master]
GO
-- kick all users out of the db
ALTER DATABASE mydb
SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
GO

-- Take the Database Offline
ALTER DATABASE mydb SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO

-- detach the db
EXEC master.dbo.sp_detach_db @dbname = N'mydb'
GO

#4

Add permission to the folder where your .mdf file is.

将权限添加到.mdf文件所在的文件夹。

Check this name: NT Service\MSSQLSERVER

检查这个名称:NT服务\MSSQLSERVER。

And change the Location to your server name.

并将位置更改为您的服务器名称。

#5

This problem is caused by UAC (User Account Control), isn't it? Although your user account is a member of Administrators group, the UAC in Windows 7 doesn't allow you do do administrator things unless you run programs "as administrator". It is not a real bug in SQL Server or Management Studio or whatever. (Although it could possibly know the problem and ask you for elevated permissions instead of just complaining "error 5".)

这个问题是由UAC(用户帐户控制)引起的，不是吗?尽管您的用户帐户是管理员组的成员，但是Windows 7中的UAC不允许您做管理员的事情，除非您运行程序“作为管理员”。在SQL Server或Management Studio中，它不是一个真正的bug。(尽管它可能知道这个问题，并请求您提高权限，而不是仅仅抱怨“错误5”。)

#6

When you login as sa (or any Sql Server account), you're functioning as the SQL Server service account, when you're logged in as you, you have the permissions of your account. For some reason you don't have the appropriate file access but the service account does.

当您以sa(或任何Sql Server帐户)的身份登录时，您的功能是作为Sql Server服务帐户，当您以您的身份登录时，您拥有帐户的权限。由于某些原因，您没有适当的文件访问权限，但是服务帐户确实有。

#7

Run SQL Server Management Studio as an Administrator. (right click-> run as administrator) worked for me with Windows 7 - SQL server 2008 R2

作为管理员运行SQL Server Management Studio。(右击->运行管理员)为我工作的Windows 7 - SQL server 2008 R2。

#8

A SQL2005 database can be attached in this way in Windows 7:

在Windows 7中可以以这种方式连接SQL2005数据库:

start menu >
 all program >
  Microsoft sql server 2005 >
   sql server management studio >
    right click >
     run as administrator >
      click ok

And then attached database successfully completed.

然后，数据库成功完成。

#9

The sa user uses NTFS accounts SQLServerMSSQLUser$<computer_name>$<instance_name> and SQLServerSQLAgentUser$<computer_name>$<instance_name> to access the database files. You may want to try adding permissions for one or both these users.

sa用户使用NTFS帐户SQLServerMSSQLUser$ $ 和SQLServerSQLAgentUser$ $ 来访问数据库文件。您可能想尝试为其中一个或两个用户添加权限。

I don't know if solves your problem since you say you have no problems with the sa user, but I hope it helps.

我不知道是否解决了你的问题，因为你说你对sa用户没有问题，但我希望它能有所帮助。

#10

With me - Running on window 8 - RIght click SQL Server Manager Studio -> Run with admin. -> attach no problems

与我一起运行在窗口8 -右击SQL Server Manager Studio ->与admin运行。- >连接没有问题

#11

it can be fixed easly but radicaly, just go to the folder where you have stored mdf file. select file-> Right click ->click on properties and give full permissions to file for logged in user Security.

它可以是固定的，但也可以是极端的，只要转到存储mdf文件的文件夹即可。选择文件->右击->点击属性，并给予登录用户安全的全部权限。

#12

Every time I have run into this issue was when attempting to attach a database that is in a different directory from the default database directory that is setup in SQL server.

每次我遇到这个问题时，都是在试图附加一个数据库，该数据库位于SQL server中设置的默认数据库目录的另一个目录中。

I would highly recommend that instead of jacking with permissions on various directories and accounts that you simply move your data file into the directory that sql server expects to find it.

我强烈建议您不要在各种目录和帐户上使用权限，而是将数据文件移动到sql server希望找到的目录中。

#13

I just wanted to add this information as well.

我只是想添加这些信息。

http://www.mssqltips.com/sqlservertip/2528/database-attach-failure-in-sql-server-2008-r2/

http://www.mssqltips.com/sqlservertip/2528/database -连接-失败-在- sql - server - 2008 r2/

Solution

解决方案

You get this error because two different logins did the detach and attach operations. So the files, when detached, were owned by the first login, but the attach failed because the login that was used was not the owner of the mdf and ldf files.

您会得到这个错误，因为两个不同的logins执行了分离和附加操作。因此，当分离时，文件由第一个登录所拥有，但是连接失败，因为使用的登录不是mdf和ldf文件的所有者。

When we detach database files, the owner becomes the person who did the detach command, so to resolve the issue we need to change or add the other login as the owner of the mdf and ldf files.

当我们分离数据库文件时，所有者成为执行detach命令的人，因此为了解决这个问题，我们需要更改或添加其他登录作为mdf和ldf文件的所有者。

Right click on the "filename.mdf" file and select properties to check the permissions of the mdf file. Here we can see that only one account has permission to the "filename.mdf" file because that was the account that was used to detach the database.

右键单击“文件名”。文件和选择属性检查mdf文件的权限。在这里，我们可以看到只有一个帐户拥有“filename”的权限。文件，因为这是用来分离数据库的帐户。

To resolve this issue, click on the Add... button to add the other login or any other login needed and give the login Full Control. You should do this for the "ldf" file as well. Once you have completed this task click the OK button. (Note for other OS versions you may have an Edit option , click this first and then you will see the Add... option.)

要解决这个问题，请单击Add…按钮，以添加其他登录或其他登录需要，并给予登录完全控制。您也应该为“ldf”文件这样做。完成此任务后，单击OK按钮。(注意其他OS版本，你可能有一个编辑选项，点击这个，然后你会看到添加…选择。)

#14

I found this solution: Right click on folder where you store your .mdf file --> click Properties --> choose Security tab, click Edit... and give it full control. Hope this helps!

我找到了这个解决方案:右键单击你存储.mdf文件的文件夹——>点击属性——>选择Security选项卡，点击编辑…并给予充分的控制。希望这可以帮助!

#15

For what it's worth to anyone having the particular variation of this problem that I had:

对于我所遇到的这个问题的特殊变化，对于任何人来说都是值得的:

• SQL Express 2008
• SQL Express 2008
• Visual Studio 2010 Premium
• Visual Studio 2010溢价

Through the context menu of the App_data folder I had created a SQL Express database for debugging purposes. The connection string (used by NHibernate) was as follows:

通过App_data文件夹的上下文菜单，我创建了一个用于调试目的的SQL Express数据库。连接字符串(NHibernate使用)如下:

Server=.\SQLExpress;
AttachDbFilename=|DataDirectory|DebugDatabase.mdf;
Database=DebugDatabase;
Trusted_Connection=Yes;

This gave me the same "Access denied" error on the database file. I tried giving various users Full Control to the folder and files, at one point even to "Everyone". Nothing helped, so I removed the added permissions again.

这给了我在数据库文件上相同的“访问被拒绝”错误。我试着让不同的用户完全控制文件夹和文件，甚至是“每个人”。没有任何帮助，所以我再次删除了添加的权限。

What finally solved it was to open the Server Explorer in Visual Studio, then connect to the MDF, and detach it again. After I'd done that my web app could access the database just fine.

最终解决的问题是在Visual Studio中打开服务器资源管理器，然后连接到MDF，并再次分离它。在我完成之后，我的web应用程序可以很好地访问数据库。

PS. Credits go to this blog post I found while googling this particular problem, triggering the idea to attach/detach the database to solve the issue.

我在google上搜索这个问题的时候发现了这个问题，并触发了这个想法来解决这个问题。

#16

This sounds like NTFS permissions. It usually means your SQL Server service account has read only access to the file (note that SQL Server uses the same service account to access database files regardless of how you log in). Are you sure you didn't change the folder permissions in between logging in as yourself and logging in as sa? If you detach and try again, does it still have the same problem?

这听起来像NTFS权限。它通常意味着您的SQL Server服务帐户只读取到文件的访问权限(注意，无论您如何登录，SQL服务器都使用相同的服务帐户来访问数据库文件)。您确定没有在登录时更改文件夹权限，并将其登录为sa?如果你再试一次，是否仍然有同样的问题?

#17

I had the same issue when attaching a database. It wasn't a SQL issue it was an account issue. Go to the panel control/User Account Control Settings/Set to "never notify". Finally,restart the computer and it worked for me.

我在连接数据库时遇到了同样的问题。这不是一个SQL问题，而是一个帐户问题。进入面板控件/用户帐户控制设置/设置为“永不通知”。最后，重新启动计算机，它对我起作用了。

#18

I attached the mdf file by right clicking the database and removing the log file AdventureWorks2012_Data_log.ldf in the wizard . The mdf file was placed in the following location

我通过右键单击数据库并删除了日志文件AdventureWorks2012_Data_log，附加了mdf文件。在向导中的ldf。mdf文件被放置在以下位置。

    C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA

The above method helped me to resolve the issue .

上面的方法帮助我解决了这个问题。

#19

I was reading this page and they have an interesting sentence in there:

我正在读这一页，他们在里面有一个有趣的句子:

Caution: Be very selective when adding users to these roles. For example, sysadmin maps out to dbo in every database and is the equivalent of logging in using the sa account.

注意:在向这些角色添加用户时要非常谨慎。例如，sysadmin在每个数据库中映射到dbo，这相当于使用sa帐户登录。

Of course, they also have this:

当然，他们也有:

Permissions that are granted to users and roles and are database specific. All permissions are cumulative with the exception of a DENY. A denied permission at either a user level or at a role level overrides the same permission granted via other role memberships with the exception of the sysadmin fixed server role. (A sysadmin retains all permissions, even if a role they are a member of has a DENY permission.)

授予用户和角色的权限，以及特定于数据库的权限。所有的权限都是累积的，除了否认。在用户级别或角色级别上被拒绝的权限，会覆盖除sysadmin固定服务器角色之外的其他角色成员权限授予的相同权限。(sysadmin保留所有权限，即使其成员是有拒绝权限的成员。)

So if you're a domain admin and in SQL 'sysadmin' group, the world should be your crustacean.

因此，如果你是一个域管理员，在SQL“sysadmin”组中，这个世界应该是你的甲壳类动物。

Of course, according to Microsoft, you should be taking a quick look at these two pages:
Link to Database Prerequisites

当然，根据微软的说法，你应该快速浏览一下这两页:链接到数据库的先决条件。

Link to Installing Databases

安装数据库链接

You're being naughty and trying to attach them manually :) Seriously though, do you have all the prerequisites for the AdventureWorks2008 database?
I suspect this is just another Microsoft oddity/edge case, but I could be wrong.

您很淘气，并试图手动添加它们:)认真地说，您是否具备AdventureWorks2008数据库的所有先决条件?我怀疑这只是微软的另一个奇怪的例子，但我可能是错的。

#20

I moved a database mdf from the default Data folder to my asp.net app_data folder and ran into this problem trying to set the database back online.

我将一个数据库mdf从默认的数据文件夹移动到我的asp.net app_data文件夹，并遇到了这个问题，试图将数据库重新设置为在线。

I compared the security settings of the other file databases in the original location to the moved files and noticed that MSSQL$SQLEXPRESS was not assigned permissions to the files in their new location. I added Full control for "NT SERVICE\MSSQL$SQLEXPRESS" (must include that NT SERVICE) and it attached just fine.

我将原始位置的其他文件数据库的安全设置与移动文件进行了比较，并注意到MSSQL$SQLEXPRESS在它们的新位置中没有分配到文件的权限。我添加了“NT服务\MSSQL$SQLEXPRESS”(必须包括NT服务)的完全控制，并附带了良好的附件。

It appears that the original Data folder has these permissions and the files inherit it. Move the files and the inheritance breaks of course.

看起来原始数据文件夹具有这些权限，文件继承它。当然，移动文件和继承中断。

I checked another project's mdf file which I created directly into its app_data folder. it does not have MSSQL$SQLEXPRESS permissions. Hmmm. I wonder why SQL Express likes one but not the other?

我检查了另一个项目的mdf文件，我直接创建了它的app_data文件夹。它没有MSSQL$SQLEXPRESS权限。嗯。我想知道为什么SQL Express喜欢其中一个，而不是另一个?

#21

USE [master]
GO
CREATE DATABASE [DataBasename] ON 
( FILENAME = N'C:\data\DataBasename.mdf' )
 FOR ATTACH
GO

change to FOR ATTACH -- > FOR ATTACH_FORCE_REBUILD_LOG

为ATTACH_FORCE_REBUILD_LOG修改附加的>。

USE [master]
GO
CREATE DATABASE [DataBasename] ON 
( FILENAME = N'C:\data\DataBasename.mdf' )
 FOR ATTACH_FORCE_REBUILD_LOG
GO

#22

It is in fact NTFS permissions, and a strange bug in SQL Server. I'm not sure the above bug report is accurate, or may refer to an additional bug.

它实际上是NTFS权限，在SQL Server中是一个奇怪的bug。我不确定上面的bug报告是准确的，或者可能是另一个bug。

To resolve this on Windows 7, I ran SQL Server Management Studio normally (not as Administrator). I then attempted to Attach the MDF file. In the process, I used the UI rather than pasting in the path. I noticed that the path was cut off from me. This is because the MS SQL Server (SQLServerMSSQLUser$machinename$SQLEXPRESS) user that the software adds for you does not have permissions to access the folder (in this case a folder deep in my own user folders).

为了在Windows 7上解决这个问题，我通常(不是管理员)运行SQL Server Management Studio。然后，我尝试添加MDF文件。在这个过程中，我使用UI而不是在路径中粘贴。我注意到小路被我切断了。这是因为MSSQL Server (SQLServerMSSQLUser$machinename$SQLEXPRESS)用户添加的软件没有权限访问文件夹(在本例中是我自己的用户文件夹中的文件夹)。

Pasting the path and proceeding results in the above error. So - I gave the MS SQL Server user permissions to read starting from the first directory it was denied from (my user folder). I then immediately cancelled the propagation operation because it can take an eternity, and again applied read permissions to the next subfolder necessary, and let that propagate fully.

粘贴路径并在上述错误中进行结果。因此，我让MS SQL Server用户权限从它被拒绝的第一个目录开始读取(我的用户文件夹)。然后，我立即取消了传播操作，因为它可以使用一个永恒，并再次将读取权限应用到需要的下一个子文件夹，并使其充分传播。

Finally, I gave the MS SQL Server user Modify permissions to the .mdf and .ldf files for the db.

最后，我让MS SQL Server用户修改了db. mdf和.ldf文件的权限。

I can now Attach to the database files.

现在可以附加到数据库文件。

#23

I got this error as sa. In my case, security didn't matter. I added everyone full control to the mdf and ldf files, and attach went fine.

这个错误是sa。在我看来，安全无关紧要。我添加了所有人对mdf和ldf文件的完全控制，并附上了罚款。

#24

If you run sql server 2012 you can get this error by trying to attach an older version of an mdf-file. ex an mdf file from sql server 2008.

如果您运行sql server 2012，您可以通过添加一个旧版本的mdf文件来获得这个错误。来自sql server 2008的mdf文件。

#25

I have solved the problem by just move the .mdf file that you want to attach to the public folder, in my case I moved it to the users/public folder. Then I attach it from there without any problem. Hope this helps.

我已经解决了这个问题，只需移动你想要附加到公用文件夹的.mdf文件，在我的情况下，我将它移动到用户/公用文件夹。然后我把它放在那里，没有任何问题。希望这个有帮助。

#26

For those who could not fix the problem with the other solutions here, the following fix worked for me:

对于那些无法解决这个问题的人来说，下面的解决方案为我工作:

Go to your "DATA" folder in your SQL Server installation, right click, properties, security tab, and add full control permissions for the "NETWORK SERVICE" user.

在您的SQL Server安装中，点击“DATA”文件夹，右键单击，属性，security选项卡，并为“网络服务”用户添加完全控制权限。

http://decoding.wordpress.com/2008/08/25/sql-server-2005-expess-how-to-fix-error-3417/

http://decoding.wordpress.com/2008/08/25/sql服务器- 2005表达- -如何修复错误3417/

(The above link is for SQL 2005, but this fixed a SQL 2008 R2 installation for me).

(上面的链接是SQL 2005，但这是为我修改的SQL 2008 R2安装)。

Some additional info: This problem showed up for me after replacing a secondary hard drive (which the SQL installation was on). I copied all the files, and restored the original drive letter to the new hard disk. However, the security permissions were not copied over. I think next time I will use a better method of copying data.

一些额外的信息:这个问题在替换了一个次要的硬盘驱动器(SQL安装是打开的)之后出现了。我复制了所有的文件，并恢复了原来的驱动器的字母到新的硬盘。但是，安全权限没有被复制。我想下次我会用一种更好的复制数据的方法。

#27

In my case what solved the problem was the folowing:

在我的案例中，解决这个问题的是folowing:

USE [master]
GO
CREATE DATABASE [AdventureWorks2008R2] ON
( FILENAME = 'C:\Program Files\Microsfot SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\AdventureWors2008R2_Data.mdf')
FOR ATTACH_REBUILD_LOG

#28

Copy Database to an other folder and attach or Log in SQLServer with "Windows Authentication"

将数据库复制到另一个文件夹，并在SQLServer中附加或使用“Windows身份验证”

#29

I've had the same issue when re-attaching database after detaching it and moving ldf and mdf files from drive C to F.

在将数据库分离并将ldf和mdf文件从驱动器C调到F时，我遇到了同样的问题。

In order to fix it I had to add OWNER RIGHTS principal to both files and gave it full control over them in the Security tab of the Properties dialog.

为了修复它，我必须在两个文件中添加所有者权限主体，并在属性对话框的Security选项卡中对它们进行完全控制。

#30

I struggled with SSMS (2016) to attach the AdventureWorks2012 database. But had success with this code, taken from a CodeProject article by Mohammad Elsheimy:

我与SSMS(2016)进行了斗争，以附加AdventureWorks2012数据库。但是这段代码取得了成功，这是Mohammad Elsheimy在CodeProject上的一篇文章中说的:

CREATE DATABASE AdventureWorks2012
    ON PRIMARY (FILENAME='D:\Dev\SQL Server\AdventureWorks2012.mdf')
    FOR ATTACH;