Access is denied when attaching a database

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

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)

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

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.

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

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.

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!

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.

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

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

• SQL Express 2008
• Visual Studio 2010 Premium

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:

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.

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.

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.

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

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

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.

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?

I was facing same issue in VS 2019. if anyone still facing same issue then please make sure you have/do following things:

1. You should have SQL Express installed on your m/c
2. Should have SSDT installed in VS (in VS 2019- make sure to check this component while installing) for previous versions - you have to add this component externally
3. Add 'User Instance = True' to your connectionstring
4. I think its optional - open VS and SQL Express in administrative mode and login as admin to SQL Express