Fix Attach Database Error: 5123 in MS SQL Server

Attach Database Error 5123 is a type of a permission error in SQL Server. As we know that, the .mdf files are primary database files and the .ldf files represent log files associated with the primary database files. Despite attaching primary data files via SQL Server Management Studio, sometimes it is difficult to open it.

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file... (Microsoft SQL Server, Error 5123)

Microsoft SQL Server Error 5123 mostly occurs when you want to attach the database which are placed on different locations. Alternatively, this error can also be experienced when there are different logins for attaching and detaching the database.

Reasons of Microsoft SQL Server Error 5123:

1. This error can be occur if you have previously detached the database with different logins and trying to attach the database with different logins.
2. In the SQL Server this error can also occur at the time when the process currently running on the server has no proper permission to run the program folder.

To resolve this issue we need to change the permission or add the permission as the owner of the MDF and LDF files.

Resolving Microsoft SQL Server Error 5123 Attaching Database

1. Right click on the MDF file, which you want to attach.
2. Select properties to check the permissions of the MDF file.
3. Click on the Add button to grant the other login permission or and give the full login control.

You can also do this for the associated LDF file and then try to attach again.

You can try "Run as Administrator" rather giving permission to everyone.

Attaching a Database in SQL Server

There are several methods for attaching the database. Here we will discuss how to attach database using SSMS and T-SQL.

Attaching the Database Using SSMS

1. Log in to the SSMS using valid server's name and Windows Authentication.
2. Go to the Object Explorer and click on 'Databases' to see a list of existing databases.
3. Right-click on the 'Database' and select 'Attach' from the drop down menu.
4. A new Attach Databases Window will be open, now click on the 'Add' button.
5. Select the database, which you want to attach.
6. The database window will confirm the MDF file and LDF file. Now click on OK button.

Now you can see the attached databases in SQL instance.

Attaching the Database Using T-SQL

Using sp_attach_db stored procedure, we can attach the databases in the SQL Server.

sp_attach_db [ @dbname= ] 'dbname'
, [ @filename1= ] 'filename_n' [ ,...16 ]

[ @dbname= ] Is the name of the database to be attached to the server.
[ @filename1= ] 'filename_n' Is the physical name, including path, of a database file.

Conclusion

In this article, we discussed about the reasons of reasons of Microsoft SQL Server Error 5123 attaching database and know how to troubleshoot the error.