附加数据库时访问被拒绝
我使用的是 SQL Server 2008 开发人员版.我试图附加 AdventureWorks2008 数据库.
I am using SQL Server 2008 developer edition. I was trying to attach the AdventureWorks2008 database.
当我尝试附加时，收到访问被拒绝"错误.根据事件日志，它来自O/S:
When I tried to attach, I received an "access is denied" error. According to the event log, it came from the O/S:
打开失败:无法打开文件 D:ProjectDataAdventureWorksAdventureWorksLT2008_Data.mdf 为文件号 0.操作系统错误:5(访问被拒绝.).
Open failed: Could not open file D:ProjectDataAdventureWorksAdventureWorksLT2008_Data.mdf for file number 0. OS error: 5(Access is denied.).
我以为是NTFS 问题"，但系统(和我)对这两个文件都有修改权限.
I thought "NTFS problem", but System (and I) have modify access to both files.
我发现如果我以sa身份登录可以成功附加数据库，但我的用户帐户不起作用.
I found that I can successfully attach the database if I log in as sa, but my user account won't work.
我是我机器上本地管理员组的成员，我在 SQL Server 实例中担任 sysadmins 角色.
I am a member of the local administrators group on my machine, and I am in the sysadmins role in SQL Server instance.
知道为什么我必须以 sa 的身份登录吗?
Any idea why I had to be logged in as sa?
推荐答案
感谢您的所有评论.你们中的一些人帮助我找到了答案.这是我发现的:
Thank you for all of the comments. Some of you helped to lead me to the answer. Here's what I found:
这是一个 NTFS 权限问题，而不是 SQL 问题.此外，它看起来有点像错误(而且是可重复的).
It was an NTFS permission problem, and not a SQL problem. Further, it looks kind of bug-like (and it's repeatable).
问题:我使用的帐户对 mdf 和 ldf 文件具有完全控制的 NTFS 权限.但是，它通过组成员身份获得了这些权限(本地管理员组具有权限，我的帐户是本地管理员的成员).(我验证了权限)
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)
如果我尝试进行附加，以我的身份连接到 SQL Server(我在管理员组中)，它会因 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.
但是，如果我将本地管理员组具有的相同文件权限直接授予我的域帐户，那么我可以毫无问题地附加.
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).
因此，看起来错误的发生是因为某些代码(在 SQL Server 或 Management Studio 中)检查用户帐户拥有的权限，但它并没有检查用户的组权限帐户继承.
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.
这对我来说听起来很奇怪，但我可以一遍又一遍地重现它，所以我得出结论，这就是答案.
That sounds weird to me, but I can reproduce it over and over again, so I have concluded that it is the answer.
更新:我将此报告为错误:https://connect.microsoft.com/SQLServer/feedback/details/539703/access-denied-attaching-a-database-when-permissions-are-继承
相关文章