Summary: This post discusses the possible reasons behind SQL Server ‘operating system error 5 (Access is denied.)’, along with the solutions to fix the error. Troubleshooting the error manually may take too much time. You may also fail to attach a database if the MDF file has turned corrupt or is missing. Try using Stellar Repair for MS SQL software to repair the MDF file. Once the file is repaired, you can attach it to SQL Server with no or minimal downtime.
Contents
Several SQL database administrators have reported encountering SQL Server error 5123 with the message ?operating system error ‘5 (Access is denied.)’?. The error occurs while trying to attach a database, which has been placed in different locations. The complete error message reads as:
?Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc) CREATE FILE encountered operating system error 5 (Access is denied.) while attempting to open or create the physical file ?filepath?. (Microsoft SQL Server, Error: 5123)?
Possible Reasons behind Operating System Error 5 (access is denied) Error and Their Solutions
Following are the main reasons that may cause MS SQL ?access denied error? when attaching SQL database, along with their solutions:
Reason 1 ? Default Location of SQL Data Files Has Changed
SQL database files (MDF and NDF) are moved from their default location on the system drive to another location.
Solution – Check the Default Location of the Data File
Go to the default location of the file folder where the SQL database (MDF/LDF) files are stored. Check whether the database files are present in their default location or not. If they are not present, paste them in the default folder.
Note: The database should not be in use when copying the files into the default folder.
The default location for SQL data files is as follows:
C:\Program Files\Microsoft SQL Server\MSSQL{nn}.MyInstance\
Here, ?nn? stands for SQL Server version.
For example, following is the default location of DATA file of MS SQL version 14.
C:\Program Files\Microsoft SQL Server\MSSQL 14.SQLTECH2017\MSSQL\DATA
Reason 2 – SQL Server Resets File Permissions when Database is Detached
Although SQL Server administrator credentials might grant you the privilege to perform several activities, some activities require a different set of permissions.
For instance, your log-in credentials might allow you to remove the database but will throw the ?SQL Server Access denied? error message when you attempt to re-attach it. The error occurs because the SQL Server resets the file permissions when the db is detached. And so, the database file permissions must be granted explicitly to reattach the database.
After the database has been attached, the permissions are reverted to the Database Engine SID NT SERVICE\MSSQLSERVER account, and all privileges for individual log-in credentials are removed. While you can use the Database Engine SID NT SERVICE\MSSQLSERVER account to attach database files, it might not always be easy to do so.
Solution – Grant Full Access Control on the SQL Data (MDF) and Log (LDF) Files
Let?s take an example to understand and fix the problem. In this example, two administrators, Adm1 and Adm2, have sysadmin (system administrator) rights on an SQL server instance. Here, we will use the first administrator?s credentials to remove a database, followed by using the second administrator?s credentials to attach the same database.
- Step 1 ? Create the Example Database
For this example, an example database ?db1? is created on the system. The name of the database file is db1.mdf, and the name of the database log file is db1.ldf.
- Step 2 ? Check the Permissions of the db1.mdf and db1.ldf Files
Open the db1 Properties window. Under the Security tab, select the server name, and grant all permissions of the database file to all users on the server instance.
- Step 3 ? Remove the db1 Database File
Use the administrator credentials of Adm1 to detach the ?db1? database file from the server.
- Step 4 ? Check the permissions of the db1.mdf and db1.ldf files again
When you check the permissions of both db1.mdf and db1.ldf, you will notice that, under Security, full permissions are applied only to Adm1.
- Step 5 ? Attach the db1 database file
Use the administrator credentials of Adm2 to attach ?db1? back to the server. As seen in Step 4, all privileges now lie with only Adm1, which is why, when Adm2 tries to re-attach the db1 database file to the server, the system throws the SQL Error 5123: ?SQL Server Access denied?.
- Step 6 ? Apply Full Permissions to Both db1.mdf and db1.ldf Files
For ?Adm2? to be able to re-attach the db1 database file, full permissions for both the ?db1.mdf? and ?db1.ldf? files must be granted to Adm2.
Alternatively, full permissions to ?db1.mdf? and ?db1.ldf? files can be granted to the Database Engine SID NT SERVICE\MSSQLSERVER account. Under the Security tab, select ?Adm2? and grant it full permissions to the db1.mdf and db1.ldf files.
- Step 7 ? Re-attach the db1 Database File
Use ?Adm2? credentials to attach the db1 database file.
- Step 8 ? Check the Permissions of the db1.mdf and db1.ldf Files One Last Time
After the ?db1? database file has been attached, the system removes full permissions for both ?Adm1? and ?Adm2? credentials. Full permissions are now granted only to the Database Engine SID NT SERVICE\MSSQLSERVER account.
Also read: Potential causes of the ?SQL Server does not exist or access denied? error message
What To Do If the Error Persists?
If the above methods fail to fix the issue, problems within your SQL database, such as corruption in the MDF file, may cause the ?Access denied error?. In such a case, either perform a full restore from a recent database backup or repair the damaged SQL database using reliable SQL database repair software, such as Stellar Repair for MS SQL.
Key Features of Stellar Repair for MS SQL Software
- It fixes all corruption of SQL Server database and recovers inaccessible objects from MDF and NDF file.
- It carries out the highest level of non-destructive repair algorithm to preserve database integrity while recovering tables, triggers, indexes, keys, rules, and defaults.
- Trusted by SQL Server MVP
- Auto-detects the corrupt database version.
- Show a preview of the recoverable sql database.
- Saved the repaired database in 4 formats: MSSQL (.MDF), HTML. CSV and, XLS.
- Supports MS SQL 2019, 2017, 2016 , and all lower versions.
- Compatible with Windows 10 / 8 / 8.1 / 7 / Vista / XP.
Read this: How to Repair MDF File with Stellar Repair for MS SQL Software?
Conclusion
Permission denied was initiated as a security measure to prevent users from attaching files they didn?t create. Thus, use SQL log-in credentials to attach and remove database files only if you are sure which files you want to attach and what?s their source. The SQL log-in credentials use the Database Engine SID NT SERVICE\MSSQLSERVER account, and this is a good way to eliminate the SQL database access denied error message as discussed in this blog. If you?re still unable to attach the database, likely, the MDF file is not in a healthy state (and is corrupted). Try using Stellar Repair for MS SQL to scan and repair MDF files and preserve their data integrity.
