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.
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)”
Contents
Following are the main reasons that may cause MS SQL ‘access denied error’ when attaching SQL database, along with their solutions:
SQL database files (MDF and NDF) are moved from their default location on the system drive to another location.
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
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.
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.
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.
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.
Use the administrator credentials of Adm1 to detach the ‘db1’ database file from the server.
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.
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”.
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.
Use ‘Adm2’ credentials to attach the db1 database file.
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
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
Read this: How to Repair MDF File with Stellar Repair for MS SQL Software?
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.
Problem solver and Data recovery specialist. Usually share informative articles on data recovery, database corruption and ways to recover lost data.
I never imagined that a non-technical person can also solve this one panic error in MS SQL. In my case, all credit goes to the author of the free troubleshooting guide.