How to Fix SQL Server 2008 R2 Database Error 5123

Summary: If you are reading this, there is almost a 99% probability that you just encountered the SQL Server 2008 R2 Database error 5123 and cannot wrap your head around it or you are part of the 1% that love to read all about SQL Server. Regardless of which category you fall under, you are reading the right blog that describes the multiple root causes of the error and how to fix it.

The database error 5123 is a SQL Server permission error that prevents a user from attaching the database physical files (.MDF and .LDF). As we all know, the .mdf files are primary database files and the .ldf files represent log files associated with the primary database files.

While facing the above error could be intimidating, there is absolutely no need to feel threatened by it. SQL Server error 5123 is nothing but a permission error in the SQL Server 2008 R2 database.

Did you attempt to attach an inherited .MDF file via SSMS or T-SQL script?

Using T-SQL

USE [master]
GO
CREATE DATABASE [TSQL_5123] ON
( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL12.TSTSQL\MSSQL\DATA\TSQL20121.mdf’),
( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL12.TSTSQL\MSSQL\DATA\TSQL2012_log1.ldf’)
FOR ATTACH
GO

Note: Update correct path values in above command as per your system settings.

Using sp_attach_db stored procedure (deprecated)

sp_attach_db

[@dbname=] ‘dbname’

,[@filename1=] ‘filename_n’ [ ,…16 ]

[@dbname=] – Refers to the name of the database to be attached to the   server.

[@filename1=] – ‘filename_n’ Is the physical name, including path, of a database file

SAMPLE:

EXEC sp_attach_db

      @dbname = N’MyDatabase’,

      @filename1 = N’D:\DataFiles\MyDatabase_Data.mdf’,

      @filename2 = N’E:\LogFiles\MyDatabase_Log.ldf’;

Note: Update correct path values in above command as per your system settings.

Error Message:

If there are permission/privilege issues with the databases files to be attached to the SQL Server instance, you will receive a similar error as shown below:

SQL Server 2008 R2 Database Error 5123

Using SQL Server Management Studio

  • Using SSMS or the new SQL Operations Studio tool, connect to your valid server’s name with either Windows or SQL Authentication.

SQL Server 2008 R2 Database Error 5123

  • Go to the Object Explorer and click on ‘Databases’ to see a list of existing databases. Right-click on the ‘Database’ and select ‘Attach’ from the drop down menu. A new Attach Databases Window will be open, now click on the ‘Add’ button. Select the database, which you want to attach.

SQL Server 2008 R2 Database Error 5123

  • The database window will confirm the MDF file and LDF file. Now click on OK button and database files should successfully attach to the SQL Server instance as long as the logical name of the database does not already exist on the instance.

SQL Server 2008 R2 Database Error 5123

Reasons of Microsoft SQL Server Error 5123:

  • The straight forward Microsoft SQL Server Error 5123 mostly happens when you try to attach a database which are placed on different locations and do not have sufficient privileges to the folder directory.
  • Alternatively, this error can also occur when there are different logins involved in the attaching and detaching the database.

SQL Server 2008 R2 Database Error 5123

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

How to resolve:

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

Right-click on the database file xxxx.mdf reported in the error message and select Properties.

Check the permissions of the MDF file by selecting the security tab. The system displays a list of user accounts in the Groups and the username field.

SQL Server 2008 R2 Database Error 5123

On the xxxx.mdf security properties window, click Add. Add the user credentials for User2, and select the Full Control

SQL Server 2008 R2 Database Error 5123

Try to attach the file to the database by using credentials that was provided the required privileges to the file.

Now when you try to attach the database, you shouldn’t see this error. Now you can see the attached databases in SQL instance.

However, if it still persists, that might be an indication of problems within your SQL database. You’ll then need to repair the SQL database in order to overcome the error in such a case.

Recommended Solution

If you’re unable to solve SQL Server error 5123 using the above-mentioned methods, we have another option for you. Try Stellar Phoenix SQL Database Repair. This software is laced with technically advanced algorithms to fix all corruptions of SQL Server database and recover inaccessible objects from MDF and NDF database files. The software carries out the highest level of non-destructive database repair while preserving its integrity.

Free Download

In addition to solving this error, this tool can be used to rectify other SQL database problems too. Hence, we advise always keeping this one handy!

Conclusion

Though Stellar SQL Repair software should succeed in resolving the error, in the rare event that it can’t, you should then contact Microsoft customer support for professional assistance.

Comments(4)
  1. jacob April 5, 2018
    • Eric Simson April 5, 2018
    • Priyanka Chauhan April 5, 2018
  2. Katherin June 10, 2018

Leave a Reply

Your email address will not be published. Required fields are marked *