SQL server error 5123 occurs when users want to attach the database, which has been placed on different locations. This error is a kind of permission error that occurs in SQL Server. As we all know, the .mdf is a primary database file and .ldf file represents the log file associated with a primary database file. Regardless of attaching a primary data file via SQL Server Management Studio, sometimes it becomes very difficult to open it. On the other hand, this error may be encountered when there are several logins for attaching or detaching the database. Therefore, in the following section, we are going to discuss a manul procedure to resolve Microsoft SQL Server error 5123 in an absolute and reliable way.
Microsoft SQL Server Error 5123 – Reasons
Let us take a look on the reasons why Microsoft SQL server error 5123 occurs.
- This error occur if you detach the last database with different logins and then, try to attach a database with different logins.
- In Microsoft SQL Server this error generates at the time when the entire process is running on the Server but it has no permission to run a program folder.
To overcome this problem we have to change the permission or add permission as the owner of .mdf and .ldf files.
Fix MS SQL Server Error 5123 Attaching Database - Manual Steps
Follow these steps to solve SQL error 5123. Some of the steps are listed below:
- Right-click on MDF file that you need to attach
- Then, choose Properties to check the permissions of MDF file.
- Click on Add button to grant any other login permission or and provide the full login control.
Moreover, you can perform this for an associated .ldf file and again, you can try to attach.
You can also try "Run as Administrator" rather granting permission to everyone.
Attach the Database in Microsoft SQL Server
1. Using SQL Server Management Studio (SSMS)
- Log in to SSMS using a valid domain name and Windows Authentication
- Click on the Object Explorer and then, select 'Databases' to view the list of existing databases.
- Right-click on 'Database' and choose 'Attach' from a drop down menu
- A new Attach Database Window will appear on the screen, now click on 'Add' button
- After that, you can select the database that you want to attach.
- The database window will verify the MDF and LDF files. Then, click the OK button.
Here, you can see all the attached database files in SQL instance.
2. Using Transaction SQL (T-SQL)
With the help of sp_attach_db stored procedure, users can easily attach the databases in MS SQL Server. Let us have a look:
sp_attach_db [ @dbname= ] 'dbname'
, [ @filename1= ] 'filename_n' [ ,...16 ] [ @dbname= ] Is the name of database that can be attached to a server.
[ @filename1= ] 'filename_n' Is the physical name, which includes location of a database file.How to Solve Microsoft SQL Server Error 5123
If the above-stated method is not working well or you want quick solution to Fix SQL MS SQL Server Error 5123, then you can go for an automated approach ie SQL Database Recovery Tool. The tool recover database from corrupted SQL MDF File. Not only this, it is able to recover deleted database objects of SQL version 2017, 2016, 2015 & its below version.
Conclusion
SQL Server Error 5123 Access Denied error is one of the common error occur which SQL DBAs faces. The blog covers the possible reason behind this error and discusses the resolution to solve SQL Server Error 5123.