Microsoft sql management studio ошибка отказано в доступе

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:ProjectDataAdventureWorksAdventureWorksLT2008_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?

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

answered Jun 16, 2012 at 1:52

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.

answered Oct 1, 2016 at 7:40

answered Sep 19, 2014 at 9:12

answered Feb 27, 2014 at 14:02

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

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.

answered Mar 6, 2012 at 21:48

answered Nov 14, 2011 at 15:34

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?

answered Sep 1, 2011 at 16:42

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 FilesMicrosoft SQL ServerMSSQL10.SQLEXPRESSMSSQLDATA

The above method helped me to resolve the issue .

answered Apr 26, 2012 at 15:03

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

answered May 16, 2015 at 8:23

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

answered Feb 10, 2020 at 17:42

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.

answered Apr 8, 2011 at 0:12

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.

answered Aug 26, 2013 at 0:44

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

answered Dec 10, 2013 at 20:15

In my case what solved the problem was the folowing:

USE [master]
GO
CREATE DATABASE [AdventureWorks2008R2] ON
( FILENAME = 'C:Program FilesMicrosfot SQL ServerMSSQL10_50.SQLEXPRESSMSSQLDATAAdventureWors2008R2_Data.mdf')
FOR ATTACH_REBUILD_LOG

answered Feb 21, 2015 at 14:13

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.

answered Nov 30, 2015 at 14:17

I am starting to learn SQL and I have a book that provides a database to work on. These files below are in the directory but the problem is that when I run the query, it gives me this error:

Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file
«C:MurachSQL Server 2008DatabasesAP.mdf». Operating system error
5: «5(Access is denied.)».

   CREATE DATABASE AP
      ON PRIMARY (FILENAME = 'C:MurachSQL Server 2008DatabasesAP.mdf')
      LOG ON (FILENAME =     'C:MurachSQL Server 2008DatabasesAP_log.ldf')
      FOR ATTACH
    GO

In the book the author says it should work, but it is not working in my case. I searched but I do not know exactly what the problem is, so I posted this question.

To get around the access denied issue, I started SSMS as administrator and that allowed me to attach a database from my local drive. The database was created in another SQL and windows instance.

I had this problem. Just run SQL Server as administrator

Yes,It’s right.The first you should find out your service account of sqlserver,you can see it in Task Manager when you press ctrl+alt+delete at the same time;Then,you must give the read/write privilege of «C:MurachSQL Server 2008Databases» to the service account.

I solve this problem by adding Full control permission for both .mdf and .ldf files for Users group.

The problem is due to lack of permissions for SQL Server to access the mdf & ldf files. All these procedures will work :

1. you can directly change the MSSQLSERVER service startup user account, with the user account who have better privileges on the files. Then try to attach the database.
2. Or you can assign the user to the file in security tab of the mdf & ldf files properties with read and and write privileges checked.
3. Startup with windows administrator account, and open SQL Server with run as administrator option and try to login with windows authentication and now try to attach the database.

For me it was solved in the following way with SQL Server Management studio
-Log in as admin (I logged in as windows authentication)
-Attach the mdf file (right click Database | attach | Add )
-Log out as admin
-Log in as normal user

answered Sep 11, 2014 at 19:27

Even if you do the following steps you COULD get the same error message.

1. login as SA user (SSMS)
2. Edit the file permissions to say "everyone" full access (windows folder)
3. Delete the Log file (Windows Exploring  (this was what I had done per advise from some msdn forum) 

I still GOT the permission error, but then I noticed that in the Attach screen, the bottom section STILL showed the LOG file, and the error message remained the same.

Hope this helps someone who did the same thing.

answered May 21, 2014 at 0:18

It means the SSMS login user does not have permission on the .mdf file. This is how it has worked for me:

I had opened the SSMS (Run as administrator) and login as an administrator user, database right-click attach, click add, select the .mdf file, click Ok. Done.

answered Jul 15, 2020 at 22:44

answered Jul 17, 2017 at 7:45

If you get this error on an .MDF file in the APP_DATA folder (or where ever you put it) for a Visual Studio project, the way I did it was to simply copy permissions from the existing DATA folder here (I’m using SQL Express 2014 to support an older app):

C:Program FilesMicrosoft SQL ServerMSSQL12.SQLEXPRESS2014MSSQLDATA

(note: your actual install path my vary — especially if your instance name is different)

Double click on theDATA folder first as administrator to make sure you have access, then open the properties on the folder and mimic the same for the APP_DATA folder. In my case the missing user was MSSQL$SQLEXPRESS2014 (because I named the instance SQLEXPRESS2014 — yours may be different). That also happens to be the SQL Server service username.

answered Jan 24, 2019 at 1:37

For some reason, setting all the correct permissions did not help in my case. I had a file db.bak that I was not able to restore due to the 5(Access is denied.) error. The file was placed in the same folder as several other backup files and all the permissions were identical to other files. I was able to restore all the other files except this db.bak file. I even tried to change the SQL Server service log on user — still the same result. I’ve tried copying the file with no effect.

Then I attempted to just create an identical file by executing

type db.bak > db2.bak

instead of copying the file. And voila it worked! db2.bak restored successfully.

I suspect that some other problems with reading the backup file may be erroniously reported as 5(Access is denied.) by MS SQL.

answered Nov 27, 2019 at 8:49

In linux, I went to /var/opt/mssql/data/ folder and opened a terminal with sudo then, changed my *.mdf and *.ldf file permissions as below in which you replace yourDB with your Database file name and myUser to currently logged username:

chmod 755 yourDB.mdf
chown myUser yourDB.mdf

chmod 755 yourDB.ldf
chown myUser yourDB.ldf

After that, it was reconnected without any issue.

answered Jan 8, 2020 at 8:07

1. Run SQL Server management studio as Administrator
2. Log in as Windows user
3. Remove log file if you have only MDF file (haven’t log file)

With 3 items in the above checklist, you will remove almost problems related with attach database task.

answered May 22, 2021 at 5:17

If you’re using Storage Gateway — SMB (S3)
Do this from the management studio

1. EXEC xp_cmdshell ‘net use X: 100.155.16.6mystoragegatewayfolder xxmysuperpassxx /user:sgw-445577smbguest /persistent:yes /y’

2. EXEC XP_CMDSHELL ‘Dir X:’ (this should show you the Directory info, serial no etc)

3. Mount the drive (This PC — > Mount network drive, using the same info above)
   Test run a backup job using scheduled backup, this will force to use the sql server agent and you can see where it’s writing and whats the issue if any.

answered Aug 9, 2022 at 4:51