Summary: Read this blog to learn about the possible reasons leading to SQL Server 2008 R2 Database error 5123. Also, explore the methods to fix the 5123 error by granting full permissions to the database and then attempting to attach it by using SSMS or T-SQL. For a quick solution to resolve the error and regain access to your database, try using Stellar Repair for MS SQL software. The software can help you recover all the objects from corrupted database (MDF/NDF) files of SQL Server version 2019, 2017, 2016, 2014, 2012, 2008 R2, and lower versions.
Contents
The ?SQL Server error: 5123, severity 16, state 1? occurs when attaching database in SQL Server 2008 R2 or higher versions. This may happen when the database you?re trying to attach is placed in a different location.
Reading the complete error message ?CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file?? clearly indicates that it occurs due to a permission issue.
Now let?s discuss in detail the possible reasons leading to SQL Server error 5123.
Reasons Behind Microsoft SQL Server Error 5123
You may receive the error due to any of these reasons:
- When you try to attach a database, which is placed on different locations, and do not have sufficient privileges to the folder directory.
- Alternatively, this error can also occur when different logins are involved in attaching and detaching the database. For instance, the Windows login used to detach the db is different from the Windows login used for reattaching the db.
How to Resolve SQL Server 2008 R2 Database Error 5123?
To resolve this issue, you will need to change or add permissions as the owner of the MDF and LDF files. To do so, follow these steps:
Step 1: Grant Full Permissions on Database Files
- Right-click on the database file xxxx.mdf reported in the error message, and then select Properties.
- Check the permissions of the MDF file by clicking the Security tab. The system displays a list of user accounts under Group or user names.
- On the ?xxxx.mdf security properties? window, click Add. Add the user credentials for User2, and check the Full Control checkbox under Allow. Click OK.
- Try to attach the file to the database using the same credentials used to detach the database.
Step 2 ? Attach the SQL Database
Try attaching the database in SQL Server using any of the following methods:
Method 1: Using SQL Server Management Studio (SSMS)
- Using SSMS or the new SQL Operations Studio tool, connect to your valid server?s name with either Windows or SQL Authentication.
- 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.
- When an ?Attach Databases? window opens, click on the ?Add? button. In the dialog box that appears, select the database you want to attach, and then click ?OK?.
- The database window will confirm the MDF and LDF files. Now, click ?OK.? The database files should successfully attach to the SQL Server instance as long as the database?s logical name does not exist on the instance.
Method 2 – Using T-SQL
Attach your SQL database 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 the above command as per your system settings.
After executing the above steps, you won?t experience any issue when trying to attach database failed for SQL Server 2008 R2. You can see the attached databases in SQL instance.
What if the Error Persists?
If the above solution failed to fix the error, that might be an indication that there is a problem within your SQL database. In such a case, you?ll need to repair the SQL database to overcome the error try Stellar Repair for MS SQL. This SQL recovery software is purpose-built to fix all types of corruption errors of SQL Server database. It also helps recover inaccessible objects from MDF and NDF database files. The software repairs a database file while preserving its integrity.
End Note
Though SQL database repair software should succeed in resolving the error, in the rare event if it fails, contact Microsoft customer support for professional assistance.
