Complete Guide to Fix SQL Database Access Denied Error
When We talk about data management, then most of the organization prefer to use Microsoft SQL Server. It is the most powerful and efficient relational database management system. This application helps the users to manage tons of data in just a few clicks. Sometimes SQL users have to face SQL server attach database access denied error due to conflict of permissions.
Let Us Understand the Problem
Two DBA ( Database administrators ) is the member of of the local server administrators groups on Microsoft SQL server machine. They both have the sysadmin rights on the SQL server instance.
Please consider the following scenario.
- DBA one detached the database by using his/her windows login.
- DBA two tries to attach the same database by using sysadmin rights. And then the user faces the SQL database access denied error.
In case if you have SQL server administrator credentials than it allows the SQL users to perform the number of tasks. But there are some activities which require a different set of permissions. For example, in case if you have the SQL server login credentials then it allows the user to detach the database but when the user tries to attach the database then the user has to face SQL server attach database access denied error.
This problem tackling blog will discuss how to deal with the SQL database access denied problem. We will understand the solution to this problem by taking an example.
Know-How to Deal With SQL Server Attach Database Access Denied Error
In this example, we will be considering two database administrators, DBA 1 and DBA 2. Both have the sysadmin rights on an SQL server instance. We will use the DBA1 login credentials to remove the database and DBA2 administrators credentials to attach the same database.
Step 1: First create the Example database
To understand this, we have created the example database named Database1 is created on the system. So the name of the primary database file will be like this Database1.mdf and name of the log file is database1.ldf
Step2: Now check the permission of database1.mdf and database1.ldf
For this, the user has to open the database1 properties window. In the Security tab, choose the server name and grant all the permissions of the SQL database file to all users.
Step3: Now detached the database1 file
By using the DBA1 credentials detached the database1 file from the server.
Step 4: Now again check the permission of the database1.mdf and database1.ldf
So when the user checks the permission for database1.mdf and database1.ldf then the user will notice that, under security, DBA 1 has the full permissions.
Step 5: Now attach the database1 file
Use the DBA2 credentials to attach the database1 back to the SQL server. As we know that permissions are applied to the DBA1. So this is the reason the user has to face SQL Server Attach Database Access Denied Error.
Step 6: Apply the file permission to both the database1.mdf and database1.ldf files
If DBA2 wants to reattach the database1 then full file permission for both the database.mdf and database.ldf must be granted to DBA2. For this under the security tab choose DBA2 and grant full permissions to the database1.mdf and database1.ldf
Step 7: Now-Again reattach the database
Use DBA2 credentials to attach the database file.
Important Note: In case if you are still facing this problem then maybe there is some problem with your SQL database. If you are facing any corruption issue then the user can take the help of SQL Database Recovery tool. By using this software the user can easily fix all the corruption problems. This software is compatible with SQL server 2017 and its below version.
Final Words
Sometimes the user has to face SQL server attach database access denied error. So in this article, we have resolved the problem by taking an example. In case if the user is facing any type of MDF file corruption problem then the user can take the help of an automated solution to recover corrupted MDF file easily. For complete information the user can try the demo version of this software.