Access is denied when attaching a database

Question

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

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

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

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

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

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

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

Edible

Is the MDF file encrypted by any chance? – Melosa

Nope -- the real curiosity for me is that it works fine if I log in as sa (using Management Studio), but it does not work if I use my local admin account. My account is an admin, a domain admin, and it is the account that I was logged in under when I installed SQL Server (during setup there was an option to make my current account a sysadmin, and I did so). – Edible

This is how UAC works in W7, no surprise. – Parturifacient

@AlKepp Nope -- not a UAC thing. Just logging in as sa corrects (SQL server account, has nothing to do with UAC) the problem. Also, just by being a member of the local admins group, I get my permissions -- I do not have to elevate in order for my AD credentials to work. – Edible

Answer 1

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

Edible

If, like me, you are using Windows 7, you will have to run SQL Server Management Studio as Administrator to avoid getting this error. – Rohrer

Reproduced on Win7 Pro using SS2008 Express. Same issue for both sqlcmd and SSMS. == Meldung '5120', Ebene '16', Status '101', Server 'DAGO\SQLEXPRESS', Zeile 1 - 'Die physische Datei 'D:\data\mssql\drei.mdf' kann nicht geöffnet werden. Betriebssystemfehler 5: '5(Zugriff verweigert) == Granting full access to the user (who is a member of the local admin group, which has access) fixes the problem. Also, running sqlcmd (or SSMS, I guess) as Administrator doesn't produce this error. – Peristome

Anthony Highsky has the answer. Just need to be sure to run Management Studio as an Administrator. – Mercuric

Same problem, and solution, for me. 2008R2, Win 7, etc. Just added myself explicitly to the security list, and it worked. I suppose that SQL Server can read them, once attached, but not under my credentials when attaching? – Kennard

Reproduced in Windows 8.1 Preview 64bit with SQL Server 2012 Development 64bit. This solution worked for me. – Tonita

Running Management Studio as administrator did NOT work for me. This error occurs when attempting to start the windows service. – Sarge

This helped me on Win 7 Pro using 2012 and 2008 SQL Servers. One other thing that I had to change was that both servers (from the services control panel) needed run as NT Authority and not my username or the access denied error would show up even if file permissions were totally open. – Disappear

I had to change the Log On As setting on Windows Service MSSQLSERVER from Network Service to Local System Account and restarted the service. And it works now. – Encomiastic

I had this problem moving databases to a new folder. Turned out I needed to grant the NT SERVICE\MSSQLSERVER permissions on the new folder - msdn.microsoft.com/en-us/library/jj219062.aspx – Distressed

Giving the 'NT SERVICE\MSSQLSERVER' account full control on the MSSQL\DATA directory full control fixed the issue in my case. That's the account running the sqlserv.exe process. – Cope

Removing the part of the string referring to a DB file worked for me. <!--<add name="WebAPIDemoContext" connectionString="Data Source=DESKTOP-QH5016A; Initial Catalog=WebAPIDemoContext-20131229155926; Integrated Security=True; MultipleActiveResultSets=True; AttachDbFilename=|DataDirectory|WebAPIDemoContext-20131229155926.mdf" providerName="System.Data.SqlClient" />--> <add name="WebAPIDemoContext" connectionString="Data Source=DESKTOP-QH5016A; Initial Catalog=WebAPIDemoContext-20131229155926; Trusted_Connection=True;" providerName="System.Data.SqlClient" /> – Flutist

Supposedly it's a feature to ensure the login attaching the database has permission to read the file and is not just leveraging the (possibly higher) permissions of the service account. See mssqltips.com/sqlservertip/4542/… . I had moved the mdf files in Windows Explorer, so I had to use the Run as Administrator trick to restore them. – Perichondrium

@RobertCalhoun It makes sense to have the check, but it still seems buggy because my account did have permissions to the file. I had permissions via an AD group I belonged to. It worked if I directly granted the same permissions to my user account instead of relying upon the group membership. That's the part that seems buggy. LIke they are reading the account permissions but not the account group permissions. – Edible

The link in the answer is broken, as of 20th Feb 2024. – Merciful

Answer 2

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

SQL SRV EXPRESS 2008 R2. Windows 7

Briny

Running Management Studio as administrator did NOT work for me. This error occurs when attempting to start the windows service. – Sarge

Running as Administrator is first step. Second step is logging to SQL Server by Windows Authentication. (This method worked for me!) – Penury

Worked for me too. Cannot express in words how tiring & frustrating the permission prompts and errors are on Windows. I AM AN ADMINISTRATOR! – Single

Worked for me too. At first I didn't think it would work because SSMS is just a UI client. I thought needed the service to be run as Admin. But running SSMS as Admin is enough. – Horotelic

Worked with SQL Server Express 2014 using management studio 2017. – Abbieabbot

OMG...I forgot about that Run as Admin!! Had my dev PC crash and had all the database on another drive and need to reattach! – Merrimerriam

Worked for me too. SQL Server 2019, SSMS 18.4 – Cluster

Worked for me - but it makes no sense that even after I opened up permissions on my MDF files to Everyone - Full, it still fails, and requires running SSMS as Admin. That's a bug. – Gerry

I suspect that SSMS is trying to be "clever" and opening up the file itself before telling the database to mount it – Vesper

I have been facing this problem for many years. This is the simplest method I have seen, and it works for me. Thanks! – Bright

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

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.

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

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

Carbo

Thanks for this! I think it's vastly easier to get it right if you are also logged in using a SQL Server Authenticated account with serveradmin privileges. – Fowliang

Unfortunately, this doesn't help me if I originally created the database as 'sa' instead of as a Windows user – Mcfarland

"Be careful when detaching the database". Tell SSMS to be careful. My problems occurred because using SSMS the Copy Database command failed leaving me in shaft city with no explanation – Centigram

Thanks, I think that actually is the quickest and the safest procedure for keeping the permission. The only caveat is found is that using the classic procedure setting OFFLINE the database you need to use the ALTER DATABASE statement with the new file position, while deattaching it after you need to use the sp_attach_db with the new file position. but works very well.ty – Doddering

Answer 4

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

Check this name: NT Service\MSSQLSERVER

And change the Location to your server name.

Gristede

To find the exact account name, since it can vary from instance to instance, run this: SELECT servicename, service_account FROM sys.dm_server_services. – Publicspirited

Answer 5

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

Chloropicrin

This answer should be upvoted. Running the SSMS as Administrator is a work around that replicates this answer. Microsoft reports this as "expected behaviour" here: link – Aweinspiring

Isn't this the same as MandoMando's answer? – Abbieabbot

Answer 6

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

Parturifacient

Answer 7

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

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.

Lyte

This worked with SQL Server 2016 with Management Studio 2008 R2 on Windows 10 :) – Danaedanaher

Answer 8

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.

Utrillo

NTFS issue was the first thing that I thought as well, but that does't seem to be the issue: I am a member of the local admins group, and I verified that admins have "full control" permissions on the mdf and ldf files. Also, I am the owner of the files -- I had just created a directory and copied the mdf/ldf files to their location myself. – Edible

@JMarsch: @Distressed is saying that 'sa' has a set of SQLSERVER RIGHTS -- not NTFS rights -- which your account does not have. – Assignat

@Trevoke: I'm with you. If that's the case, then what rights would I need to assign to my user account? (I'm already assigned to the sysadmin role) – Edible

Old answer, this, but for people like myself five minutes ago: you can find the exact service user name by running SELECT servicename, service_account FROM sys.dm_server_services – Publicspirited

Answer 9

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!

Labrador

Answer 10

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.

Christchurch

Answer 11

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.

Leoraleos

Answer 12

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

Triphammer

Answer 13

I just wanted to add this information as well.

http://www.mssqltips.com/sqlservertip/2528/database-attach-failure-in-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.

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.

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.

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

Coruscate

I changed my connection in SSMS to match the user who performed the detach, and i was able to perform the attach. – Undercroft

Answer 14

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.

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.

Kilo

In a lot of situations, I would agree with your point, but with SQL server, you often want to be able to locate your databases on different spindles or volumes for scaleability. In fact, it's a common practice to put the transaction log on a separate spindle from the database in order to improve transaction throughput. – Edible

@JMarsch: Yes.. the directories are actually configurable thorugh the Server Properties > Database Settings tab for default data and log locations... – Kilo

That covers defaults, but that's only defaults. It is completely acceptable to put dbs elsewhere, and really not even that uncommon if you have your server managing more than 1 actively used database. – Edible

I have this same problem even with the default sql server directory: c:\Program Files\Microsoft SQL Server\MSSQL10_50.SPATIAL_IM\MSSQL\DATA\mydb.mdf on win7. – Carbo

Answer 15

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

Amato

Answer 16

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.

Supersensible

Answer 17

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.

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.

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?

Plexus

This solution worked for me on Windows 10 and SQL Server 2017 when moving a log file to a separate disk. In my case the username was "NT SERVICE\MSSQLSERVER" – Waitabit

Answer 18

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.

Assignat

+1 because your comment helped me to find the answer. I will post my findings to this thread. BTW (I was being "naughty" due to very strange policies where I work -- the adventureworks database is distributed as an exe. I can't download exe's. (I can download zip files and MSI files, so I don't see how the exe filtering really does anythign other than get in the way, but those are the rules). Anyway, I could get the raw mdf files as zips from codeplex, and that's when I ran onto this little curiosity. – Edible

Answer 19

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?

Refractory

In my case, no -- I re-did it serveral times to make sure. The issue was that my account only had access to the files through a level of indirection -- I was a member of group Domain Admin. Domain Admin was a member of the Local Administrators group on the machine, and Local Admins (and system) had full control to the folder. (so there were 2 levels of group indirection). If I assigned permissions to myself directly, it worked, if I removed them, I could still copy/delete the files from Explorere, etc, but SQL Server could not load them. – Edible

When try to attach database. Log in as Windows authenticated user will help us overcome permission on database files. (This case, MS SQLServer instance in disk what has Windows OS). – Earphone

Answer 20

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.

Laflam

Answer 21

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

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

The above method helped me to resolve the issue .

Uncut

Answer 22

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

Santana

Answer 23

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

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

Ministerial

Answer 24

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

Sesquicarbonate

Answer 25

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.

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

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.

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

I can now Attach to the database files.

Ironbound

Answer 26

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.

Operatic

I think that part was relatively self explanatory. it would be good to know how to sort it out. – Behrens

Answer 27

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.

Tyndall

Answer 28

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.

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

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

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.

Sarge

Answer 29

In my case what solved the problem was the 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

Ozoniferous

Answer 30

I've had the same issue when re-attaching database after detaching it and moving ldf and mdf files from drive C to 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.

Doucette

Answer 31

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

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

Liggett

Answer 32

This may help someone in the future. Our SQL server had 2 instances. I was trying to attach to an MDF in the 2016 instance but kept getting the access denied error.

I was getting the error because the database was already attached in a 2019 instance on the same server. The permissions on the .MDF showed a user MSSQL$SQL2019, which lead me to look for another instance.

Once I connected to the 2019 instance, I was able to see the database was already attached and queryable.

Merciful

Answer 33

Run this prompt cmd as admin:

icacls "C:\Path\to\Your\Folder" /grant Everyone:F /T

Albite