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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The sa user uses NTFS accounts SQLServerMSSQLUser  lt computer name gt   lt instance name gt  and SQLServerSQLAgentUser  lt computer name gt   lt instance name gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

A SQL2005 database can be attached in this way in Windows 7   start menu  gt   all program  gt    Microsoft sql server 2005  gt     sql server management studio  gt      right click  gt       run as administrator  gt        click ok   And then attached database successfully completed

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Add permission to the folder where your  mdf file is   Check this name  NT Service MSSQLSERVER  And change the Location to your server name

User · Answer

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

User · Answer

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

User · Answer

I was facing same issue in VS 2019  if anyone still facing same issue then please make sure you have do following things     You should have SQL Express installed on your m c 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 Add  User Instance   True  to your connectionstring I think its optional - open VS and SQL Express in administrative mode and login as admin to SQL Express

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt computer name gt   lt instance name gt  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

[sql-server] Access is denied when attaching a database

Examples related to sql-server

Examples related to security

Examples related to administration