You may encounter the error “Operating system error 5: Access is denied” while restoring backup, creating backup, or accessing/creating the database in SQL Server. The error occurs when the SQL Server fails to read the file you are attempting to access. The complete error message looks like this:
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file “R:\productions\stellar_database.mdf”. Operating system error 5: “5(Access is denied.)”.
Msg 1802, Level 16, State 7, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
This error prevents you from accessing the SQL database or backup file. Below, we will see the reasons that can lead to such an error and see how to fix it.
Causes of the Operating System Error 5: 5 (Access is denied) in SQL Server
The “Access is denied” error in SQL may occur due to the following reasons:
- Using the incorrect path of the backup file while restoring backup.
- SQL Server database engine service account does not have permissions to read/write the folder where your backup file is saved.
- Issue with the backup file or incorrect backup file.
- Not running SSMS with Administrator permissions.
- Database (MDF/NDF) file is corrupted.
- Backup file is not in a compatible format.
Solutions to Fix the Error “Operating System Error 5: 5(Access is denied) in SQL Server
The error “Access is denied” in SQL Server can occur if there is some issue with the backup file you’re trying to access. Check whether you have selected the correct backup file with correct name. Sometimes, simply changing the backup file location can fix this error. You can also try changing the location of the file. If the error persists, then follow the below solutions to resolve this SQL Server error.
Solution 1: Check the File and Folder Path
You can get the Operating system error 5: ‘5(Access is denied)’ in SQL Server, if there is an issue with the backup file path – either the path does not exist or it is not valid. Make sure the backup file path is correct and accessible from the SQL Server instance. If you’re using the T-SQL command to restore the backup, then check if you’ve entered the correct backup file path in the query.
Solution 2: Check and Grant the Required Permissions
If you don’t have sufficient permissions on the folder where your backup file is saved, then you may not able to access or restore the backup file and encounter the error. You can check and provide the right permissions to fix the issue. Here’s how to do so:
- Go to the location on your system where the production folder (containing the backup file) is stored, right-click the folder, and select Properties.
- Verify if the user trying to restore the backup file has permissions to access the folder.
- If not, click on the Edit button. If the user is not in the list, click the Add button to add the user.
- You can enter the object name. You can use the Check Names option to verify if you entered it correctly.
- If you do not know the name, press the Advanced button to search for the user’s name.
- Write your query with the start name or description. In Object Types, you can filter to look at users only. In Locations, you can select a computer or an Active Directory domain.
- Once added, make sure to provide Modify permission. Click OK.
Note: If you do not have permission to grant permissions for the folder, you can ask the system administrator to grant the required permissions.
Solution 3: Run SSMS as Administrator
The “Access is denied” error can also occur if you are not running the SQL Server Management Studio (SSMS) as an administrator. To run SSMS with admin rights,
- Open the Windows menu, look for the SSMS, and right-click on it.
- Select the option Run as administrator.
Solution 4: Check SQL Server Database Engine Service Account Permissions
The Service Account, under which your SQL Server Service runs, must have the permissions to read and write files in the directories where the database files are stored. If it lacks such permissions, the server will fail to read, write, attach, and detach the database files, leading to the “Access is denied” error. You can check and ensure that your SQL Server Service Account has the required permissions. If you don’t know the service account under which your SQL Server runs, run the below command:
SELECT servicename, service_account
FROM sys.dm_server_services;
Once you know the Service Account and Service Name, then check and grant the required permissions. You can simply add the Administrators group to the security permissions for the data directories where the data and log files are saved by using the SSMS.
Solution 5: Repair your Database File
SQL Server may fail to access the database file, if it is corrupted or damaged. In such a case, you can try restoring the database from backup. If you do not have a current backup, then you can repair the database by using the DBCC CHECKDB command. Here is the syntax:
DBCC CHECKDB
[ ( database_name | database_id | 0
[ , NOINDEX
| , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
) ]
[ WITH
{
[ ALL_ERRORMSGS ]
[ , EXTENDED_LOGICAL_CHECKS ]
[ , NO_INFOMSGS ]
[ , TABLOCK ]
[ , ESTIMATEONLY ]
[ , { PHYSICAL_ONLY | DATA_PURITY } ]
[ , MAXDOP = number_of_processors ]
}
]
]
Note: The DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option can help you repair the database but can cause data loss.
Solution 6: Repair your Backup File
If your backup file is corrupted, then you may fail to restore it and get the “Operating system error 5 – Access is denied” error. In such a situation, you can use Stellar Repair for MS SQL Technician – a professional SQL repair software that can recover all the data from corrupt SQL database backup (.bak) file. The software supports all SQL backup types, including Full Backup, Differential Backup, and Transaction Log Backup. It can also repair corrupted database (MDF/NDF) files and recover all the objects, including deleted records, with complete precision. The tool is compatible with MS SQL Server 2022, 2019, and lower versions.
Conclusion
The error “Operating system error 5: “(Access is denied)” in SQL Server can occur due to various reasons. You can follow the methods mentioned in this post to resolve the error. If the backup file or the database file you are trying to access is corrupted or damaged, then you can use Stellar Repair for MS SQL Technician. This software is designed to repair corrupt or damaged SQL database and backup files, and recover all the objects with complete integrity.
Was this article helpful?
