{"id":8922,"date":"2017-08-02T06:12:02","date_gmt":"2017-08-02T06:12:02","guid":{"rendered":"https:\/\/www.stellarinfo.com\/blog\/?p=8922"},"modified":"2024-03-26T11:42:16","modified_gmt":"2024-03-26T11:42:16","slug":"fix-sql-server-access-denied-error","status":"publish","type":"post","link":"https:\/\/www.stellarinfo.com\/blog\/fix-sql-server-access-denied-error\/","title":{"rendered":"How to Get Rid of SQL Database Access Denied Error"},"content":{"rendered":"

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:<\/p>

<\/p>

“Failed to retrieve data for this request. (Microsoft.SqlServer.Management.<\/em>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)”<\/em><\/p>

\n
\"SQL
Figure: SQL Database Access Denied Error<\/figcaption><\/figure>\n<\/div>

Possible Reasons behind Operating System Error 5 (access is denied) Error and Their Solutions<\/strong><\/h2>

Following are the main reasons that may cause MS SQL ‘access denied error’ when attaching SQL database, along with their solutions:<\/p>

Reason 1 – Default Location of SQL Data Files Has Changed<\/strong><\/u><\/strong><\/h3>

SQL database files (MDF and NDF) are moved from their default location on the system drive to another location.<\/p>

Solution – Check the Default Location of the Data File<\/strong><\/h4>

Go to the default location of the file folder where the SQL database (MDF<\/strong>\/LDF<\/strong>) 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.<\/p>

Note:<\/strong> The database should not be in use when copying the files into the default folder.<\/p>

The default location for SQL data files is as follows:<\/p>

C:\\Program Files\\Microsoft SQL Server\\MSSQL{nn}.MyInstance\\<\/strong><\/p>

Here, ‘nn<\/strong>’ stands for SQL Server version.<\/p>

For example, following is the default location of DATA file of MS SQL version 14.<\/p>

C:\\Program Files\\Microsoft SQL Server\\MSSQL 14.SQLTECH2017\\MSSQL\\DATA<\/strong><\/p>

\n
\"SQL
Figure: SQL data folder<\/figcaption><\/figure>\n<\/div>

Reason 2 – SQL Server Resets File Permissions when Database is Detached<\/strong><\/h3>

Although SQL Server administrator credentials might grant you the privilege to perform several activities, some activities require a different set of permissions.<\/p>

For instance, your log-in credentials might allow you to remove the database but will throw the “SQL Server Access denied<\/strong>” 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.<\/p>

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<\/strong> account to attach database files, it might not always be easy to do so.<\/p>

Solution – Grant Full Access Control on the SQL Data (MDF) and Log (LDF) Files<\/strong><\/h4>

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.<\/p>