MS SQL Server Error 5120 Access Denied – Resolved
SQL Server service accounts with insufficient permissions often lead to SQL Server error 5120 access denied problem. Whenever users try to attach or detach the database from the server, due to not having access to the MDF files, the server displays this error. Yes, this simple problem creates an environment of panic across SQL users. However, the solution to this is very simple without any commands needed.
To fix this error, let’s go through all of its reasons as understanding the root cause is just like fixing the problem halfway through.
Microsoft SQL Server Error 5120 Causes
There are various reasons why SQL Server faces this 5120 error code and almost all of them are stated here. Any of these causes can trouble users but fixing them is no big deal. This is why just after the causes, we have mentioned all the solutions available.
- Wrong User Account: Accessing and trying to attach the database with a false account or an account without admin privileges often shows this error.
- Insufficient Permissions: Not having permissions to execute operations like attaching database or detaching database causes access denied.
- Damaged MDF Data File: There mioght be cases when the MDF file have caught corruption issues leading to improper database functioning.
- Memory Shortage & False Configuration: No space in storage or false configuration sending false information regarding access geenrates this error.
All Solutions to Fix SQL Server Error 5120 Access Denied
There are four ways for users to fix the issues. Any of one of all these will surely resolve this error in the SQL database. In different scenarios, users can select their method wisely where the error cause also plays a significant role.
- Service Account Access Providing Method
- Admin Account SSMS Method
- Advanced Software Method
- Enable Trace Flag 1802
Let’s learn these solutions one after another to fix SQL error 5120 attach database issue.
#1 Provide Access to SQL Service Account
Step-1. Launch File Explorer on your system & Navigate to the location of MDF & LDF files. By default, these files are stored in this location:
e:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA
Step-2. Right-click the MDF file & Select the Properties option to fix MS SQL error 5120.
Step-3. Click on the Security tab >> Hit the Edit button to proceed.
Step-4. Hit the Add button to Add a Service Account in permissions for the database.
Step-5. SQL Server Configuration Manager Log on As section shows which account is running the service.
Step-6. Provide Full Control to the file once added.
Repeat the same procedure for the LDF file as well to fix the issue permanently.
#2 Launch SSMS with Admin Account
To resolve this SQL Server error 5120, simply try a troubleshooting tip. Just close the SQL Server Management Studio and then re-open it with admin credentials. This way, it wil allow all the operations without any privileges barrier.
Running as an Administrator option will give all the permissions an administrator has control over.
#3 Fix SQL Error 5120 by Fixing Damaged SQL Database
In case the database is denying access due to corruption or damage in the MDF file, simply go ahead and repair MDF file in SQL Server with ease. The best-advanced solution is SysTools SQL Database Recovery Tool which even Microsoft experts recommend.
This utility can repair corrupted files with any level of corruption. Just download the utility and execute four simple steps as stated below:
Step-1. Launch the Tool >> Click on the Open button >> Add MDF files.
Step-2. Select Scan Mode >> Add NDF files >> Select SQL version.
Step-3. Set Destination platform >> Select Objects to restore.
Step-4. Hit the Export/Save button to fix SQL error 5120 attach database.
That’s it. The new MDF file will be free from all sorts of damage
#4 Enable Trace Flag 1802 for Network Stored MDF Files
In a few cases, users store their data files including both MDF & LDF in a network-attached storage. In that case, they also tend to face almost the exact same error. To get rid of it, SQL Server Configuration Manager is the right solution.
The error message looks like this:
Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file “\\serverSa023\sqlfiles\test.mdf”. Operating system error 5: (Access is denied.).
Step-1. Open SQL Server Configuration Manager > Double Click on SQL Server Service.
Step-2. Write -T1802 and Hit the Add button in the startup parameters option.
Step-3. Click on Apply >> OK >> Restart Service to enable trace
This trace 1802 has the ability to directly disable the Access Control List (ACL) permissions to attach/detach databases. Therefore, once the system is restarted, users can attach the file after enabling the trace.
Also Read: SQL Server Error 5220 Solved with Ease
The Conclusion
After discussing all of the causes as well as the solutions, we are finally going to conclude this article. In a nutshell, we can say that this SQL Server error 5120 is nothing in comparison to other SQL Server major errors. This is why, the above-mentioned solutions are more than enough for SQL users to get the best in class results.