How to Get Rid of SQL Database Access Denied Error

SQL Database Administrators are adept at dealing with most minor and major errors. However, a few errors can get tricky for them too. SQL Error 5120 stands for the “SQL database access denied error” and it primarily occurs due to a conflict of permissions.

This post is dedicated to finding out the reasons behind this error and discussing manual as well as automated ways to resolve it.

Error Introduction

“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)”

Although SQL Server administrator credentials might grant you the privilege to perform a number of activities, there are some activities that require a different set of permissions. For instance, your log-in credentials might allow you to remove the database but will throw an error when you attempt to attach it back. When you try to attach the database, the system will throw the “SQL Server Access denied” error message. This is because the file permissions for the database must explicitly grant the same log-in credentials 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 u can use the Database Engine SID NT SERVICE\MSSQLSERVER account to attach database files, it might not always be easy to do so.

To help you understand how to tackle a problem of this kind, let us create a sample scenario to know Potential causes of the “SQL Server does not exist or access denied” error message

Resolving the Error

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 the purpose of 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 reattach the db1 database file to the server, the system throws the SQL Error 5120:

“SQL Server Access denied”

Step 6 – Apply full permissions to both db1.mdf and db1.ldf files

For Adm2 to be able to reattach 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 – Reattach 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.

What if these steps don’t work?

If these steps fail to fix the issue, it might be an indication of problems within your SQL database. In such a case, either perform a full restore from a recent database backup, or repair the damaged SQL database using reliable SQL recovery software.

Key Features

1. It fixes all corruption of SQL Server database and recovers inaccessible objects from MDF and NDF file.
2. It carries out the highest level of non-destructive repair algorithm to preserve database integrity while recovering tables, triggers, indexes, keys, rules and defaults.
3. Trusted by SQL Server MVP
4. Auto-detects the corrupt database version.
5. Show a preview of the repairable data.
6. Saved the repaired database in 4 formats: MSSQL (.MDF), HTML. CSV and, XLS.
7. Supports MS SQL 2019, 2017, 2016 and all lower versions.
8. Compatible with Windows 10 / 8 / 8.1 / 7 / Vista / XP.

Conclusion

Permission withdrawal was initiated as a security measure to avoid 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.

Was this helpful?

0