Read time 5 minutes
SQL database handling is complicated even for the technically sound administrators. The generally expected smooth behavior of SQL Server is often hindered by several SQL Server errors. The main effect of these SQL Server errors is upon the database accessibility which hinders all the SQL Server operation ultimately.
Our attempt in this blog is to know about a specific SQL Server error, its occurrence, reasons and solutions. The SQL Server error which we are going to discuss is “SQL database access denied error.” This error comes with the code 5123 and thus also known as “SQL Server Error 5123”. The appearance of the error is somewhat like this.
What does this error message say?
The above SQL error literally says that the SQL database file opening process has failed or access is denied to open/create this particular file. The operation has failed to retrieve data for this particular request.
The reasons behind “SQL database access denied error”
- SQL database files (MDF and NDF) are moved from its default location on the system drive.
- Administrator does not have permissions to access that particular SQL Server database file.
Both the reasons are highly probable for this “SQL Server Access Denied” error. Let us take two scenarios to understand this.
Scenario 1 – If you have intentionally or unintentionally moved the SQL database file from its default location, then it is obvious that any SQL administrator (even with the full permissions) while trying to access this database using the Windows Authentication can face this error.
Scenario 2 – In this scenario, we assume that an SQL administrator with full permissions over the SQL database file folder removes or detaches the database from the SQL Server. Then, when another SQL administrator with rights on the SQL Server instance tries to re-attach the same SQL database file to the server, this “SQL database access denied error” appears. And the cause behind it could be the lack of SQL database permissions to the second SQL administrator.
The SQL Server error 5123 can be resolved by addressing these reasons. We would provide you the manual tips to get through this error and access your SQL database again.
Users facing this error must ensure that there SQL Server is running and that SQL Server instance is available on the network.
- Confirm the SQL Database Files Location
Go to the location C:\Program Files\Microsoft SQL Server\MSSQL. 1\MSSQL\Data and check whether both the MDF and NDF files is available or not. If not, try to paste the database files at this default location.
- Assign permissions to the administrator trying to attach the database
As mentioned in the reasons, whenever any SQL administrator detaches the SQL database using his login credentials, the whole access permissions gets removed. In that case whenever any other SQL administrator with administrative rights on the same SQL Server instance tries to attach this database again, the error “>SQL database access denied error” is received.So, you need to assign the full access permissions to that SQL administrator in order to let him attach and access the SQL database. Follow these steps.
a. Go to the default SQL database file location.
b. Right-click on the .mdf file and click on the Properties option.
c. In the Properties dialogue box, navigate to the Security tab.
d. Select the Administrator name from whose credentials you want to access the database under Group or user names section.
e. Click on Edit option and select all the permissions under Allow section.
f. Click on OK and exit the Properties page.
Note: Perform the same process for the .ldf file available at the same location.g. Now, try to access the database file with this SQL administrator login credentials.
h. Hope you succeeds this time!
We expect that the above manual tip can help you access the SQL database again. But if it is not possible to open it, then we fear that your SQL database is corrupted. Sometimes the reason behind the corruption is not certain but it could lead to damage, loss and inaccessibility of SQL Server data.
In such a condition, we offer you an efficient SQL database recovery tool, the SQL Server Recovery software, which recovers corrupt and damaged MS SQL database files. The tool supports recovery of database files of all SQL Server versions, and provides the facility to view preview of the recovered data. You can save the recovered SQL database file either to live SQL Server location or to a batch file on your system. So, users can trust this tool to repair MDF file anytime and also learn about it using the free version.
Wrapping up
The most common “SQL database access denied error” is discussed in detail along with some helpful manual solutions. In addition, a solution to deal with SQL database corruption is also suggested.