+- 0Day Forums (https://zeroday.vip)
+-- Forum: Coding (https://zeroday.vip/Forum-Coding)
+--- Forum: Database (https://zeroday.vip/Forum-Database)
+---- Forum: Microsoft SQL Server (https://zeroday.vip/Forum-Microsoft-SQL-Server)
+---- Thread: SQL Server Operating system error 5: "5(Access is denied.)" (/Thread-SQL-Server-Operating-system-error-5-quot-5-Access-is-denied-quot)
SQL Server Operating system error 5: "5(Access is denied.)" - minglavecgy - 07-31-2023
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:\Murach\SQL Server 2008\Databases\AP.mdf". Operating system error
> 5: "5(Access is denied.)".
CREATE DATABASE AP
ON PRIMARY (FILENAME = 'C:\Murach\SQL Server 2008\Databases\AP.mdf')
LOG ON (FILENAME = 'C:\Murach\SQL Server 2008\Databases\AP_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.
RE: SQL Server Operating system error 5: "5(Access is denied.)" - quiritary117810 - 07-31-2023
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:\Murach\SQL Server 2008\Databases" to the service account.
RE: SQL Server Operating system error 5: "5(Access is denied.)" - preprocessors952391 - 07-31-2023
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.
RE: SQL Server Operating system error 5: "5(Access is denied.)" - picaras388922 - 07-31-2023
The actual server permissions will not matter at this point; all looks ok.
SQL Server itself needs folder permissions.
depending on your version, you can add SERVERNAME$MSSQLSERVER permissions to touch your folder. Othewise, it has to be in the default BACKUP directory (either where you installed it or default to c:\programfiles(x)\MSSQL\BACKUP.
RE: SQL Server Operating system error 5: "5(Access is denied.)" - meritocrat457881 - 07-31-2023
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.
RE: SQL Server Operating system error 5: "5(Access is denied.)" - Thinh3936 - 07-31-2023
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.
RE: SQL Server Operating system error 5: "5(Access is denied.)" - carlenpouydo - 07-31-2023
An old post, but here is a step by step that worked for SQL Server 2014 running under windows 7:
* Control Panel ->
* System and Security ->
* Administrative Tools ->
* Services ->
* Double Click SQL Server (SQLEXPRESS) -> right click, Properties
* Select Log On Tab
* Select "Local System Account" (the default was some obtuse Windows System account)
* -> OK
* right click, Stop
* right click, Start
Voilá !
I think setting the logon account may have been an option in the installation, but if so it was not the default, and was easy to miss if you were not already aware of this issue.
RE: SQL Server Operating system error 5: "5(Access is denied.)" - impletions192237 - 07-31-2023
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
RE: SQL Server Operating system error 5: "5(Access is denied.)" - larimor312 - 07-31-2023
Very Simple Solution.
1. Login with System admin
2. copy your mdf and ldf files in "C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA" Where all other data file recides.
3. Now attach from there it will work
RE: SQL Server Operating system error 5: "5(Access is denied.)" - jaydagbniv - 07-31-2023
This is Windows related issue where SQL Server does not have the appropriate permission to the folder that contains .bak file and hence this error.
The easiest work around is to copy your .bak file to default SQL backup location which has all the necessary permissions. You do not need to fiddle with anything else. In **SQL SERVER 2012**, this location is
D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup (SQL 2012)
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup (SQL 2014)
C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Backup (SQL 2016)