Ошибка 5123 sql server как исправить

The solutions on this page can be applied to resolve 5123 error with access denied or database attach failure in MS SQL Server 2014/2012/2008 R2/2005.

When you try to attach a database in SQL Server, but get an error message, reporting that «Microsoft SQL Server, Error: 5123» as the image shown below:

You will not be able to access the SQL Server database. Check the list of reasons that cause SQL error 5123, and if you find that your problem matches the listed reasons, read on to find the solution and fix this error:

• Database Attach Failure: Attaching and detaching database data file with several different logins.
• Access Denied: Attach a database which is placed on a different location and doesn’t have sufficient privileges to the folder directory or no proper permission to run the program folder. 

Although, many experienced administrators know how to resolve the 5123 error in SQL Server. It’s still necessary to know better and more effective methods to fix and repair more SQL Server database problems. Read on, and learn how to fix SQL Server 5123 error on your own.

Manual workaround to fix MS SQL Server 5123 error

By following the below manual steps, you’ll be able to resolve the Microsoft SQL Server error 5123 quickly:

• Step 1: Run SQL Server and right-click on the MDF or NDF file that you wish to attack.
• Step 2: Select «Properties» to check the permission of .mdf or .ndf file.
• Step 3: Click the «Add» button to grant the login permission and gain full control during the login process.

Also, you can attempt «Run as Administrator» to gain permission instead of granting permission to a new user.

Recommended tools to resolve SQL Server 5123 error

If you are using MS SQL Server Management Studio (SSMS) or Transaction SQL Server (T-SQL) to configure and manage the SQL Server, you can use these two tools to resolve the 5123 error. 

Also, you can turn to a third party MS SQL Recovery tool for help.

Use SSMS to fix SQL 5123 error

• Step 1: Log in to SSMS: use valid domain credentials and Windows Authentication to log in.
• Step 2: Click on «Object Explorer», select «Database».
• Step 3: Right-click on the database that you are trying to attach, and select «Attach».
• Step 4: Click «Add» when a new attach database dialog box appears.
• Step 5: Select the database, confirm the MDF and LDF files are running alright. And click «OK» to finish.

Then, you can view all attached database files in MS SQL Server again.

If the program reminds you that the MDF file is corrupted, relax, follow the link here and apply EaseUS MS SQL recovery tool to repair corrupted MDF file immediately.

Use T-SQL to resolve MS SQL 5123 error

Transaction SQL Server with its sp_attach_db stored procedure can help you easily attach all desired databases in SQL. 

Here are the detail process of attaching SQL Server databse:

• 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

Use SQL Server recovery tool to solve MS SQL error 5123

If the above-provided methods don’t work efficiently to resolve the 5123 attach database error in SQL, you can go for a reliable SQL Server recovery tool for help. It helps you to repair corrupted MFD and NDF file in SQL Server and some other complex database corruption issues. This software enables any user to operate SQL Server repair with its clear and self-explanatory interface.

Note: Mostly, EaseUS SQL Server Recovery can always work to fix this issue. But if it fails in a rare case, you can turn to Microsoft customer support team for help.

Step 1. Run EaseUS MS SQL Recovery.

Step 2. Select the MDF/NDF file: Click «Browse» or «Search” to navigate the location of your MDF or NDF file > Click «Repair».

Step 3. Select the database objects you want to recover: When the process has completed, select the database objects you want to recover and click «Export».

Step 4. Export the database to the database or export the selected items as SQL scripts. If you choose «Export to database», you will need to enter the required information and select the destination database, either a new one or an existing one.

Hence, we would like to recommend you always to keep this software handy. In addition to solving this error, EaseUS SQL Server Recovery can be used to rectify other SQL database problems too:

• Resolve SQL Database corruption with 823/824/825 error
• Recover deleted SQL records
• Repair MDF/NDF file
• Recover ROW and PAGE compressed data
• Free to preview the repaired database before activation
• Save the repaired file in MS SQL (MDF), HTML, XLS & CSV formats
• Support transferring the license to another system (one key for three systems)

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.

How to Fix Microsoft SQL Server Error 5123 Quickly

When users try to attach a database, which has been placed in different locations, then they may get SQL Server error 5123. This error generates when permissions are given to SQL Server. As everyone know, MDF is a primary database file and LDF is the log file associated with primary files. In spite of attaching a primary database file via SQL Server Management Studio, sometimes it becomes more difficult to open it.

Table of Content

• Quick Step Solution
• Reasons for Error
• Manual Solution
• Modern Method
• Conclusion
• FAQs

Also, this error may encounter when there are different logins for attaching or detaching a database. Nevertheless, in the upcoming section, we will discuss different manual ways to let users understand how to solve SQL Server attach database error 5123 in an efficient way. Before that, let us understand the reasons why SQL error 5123 attach database occurs.

Reasons For Microsoft SQL Server Error 5123

There are so many reasons due to which 5123 error occurs. Some of them are listed below:

• This error occurs if the user detach last database with several logins and then, try to attach data file with several logins.
• In MS SQL Server this error generates when complete process is running on server, but it has no permission to run a particular program folder.

To resolve this problem, users have to change permission or add permission as an owner of MDF and LDF files.

Also Read: How to Repair Corrupted SQL Database MDF File

Fix SQL Server Attach Database Error 5123 – Manual Workarounds

There are following steps to solve Microsoft SQL Server error 5123. Some of them are listed below:

• Right-click on .mdf file that you wish to attach
• After that, choose Properties to check the permissions of .mdf file
• Click Add button to grant the login permission and provide full control during the login process

Besides, you can perform this for corresponding LDF file and then, you can try to attach again.You can also attempt Run as Administrator instead of granting permission to everyone.

Using MS SQL Server Management Studio (SSMS)

a) Now, you log in to SSMS using valid domain credentials and Windows Authentication
b) Click the Object Explorer and select Databases to view a list of all existing databases.
c) Right-click on the Database and choose Attach option from the drop down menu
d) A new Attach Database dialog box appears on the screen and click Add button
e) Then, you can select a database that you want to attach
f) A database window will confirm MDF and LDF files. Next, click OK button

Here, you can view all attached database files in Microsoft SQL instance.

Using Transaction SQL Server (T-SQL)

Through sp_attach_db stored procedure, you can easily attach all databases in SQL Server. Let us have a look:

Automated Solution to Solve SQL Server Error 5123

If the above-stated methods does not work properly and not able to solve SQL Server attach database error 5123, then you can go for a reliable and approach. One such application is SQL Repair Tool, which helps to recover corrupt .mdf and .ndf SQL server database file. The best part about this tool is that any novice user can operate it because of its brilliant and self-explanatory interface.

Download Tool Purchase Now

Some of the most interesting features of the tool those are mentioned below:

• Allows to recover all tables, functions, stored procedure, views, rules, etc.
• Recover deleted records from SQL table without losing a single bit of data
• Export selective items from MDF (Primary) or NDF (Secondary) data file
• Auto-fetch SQL Server Name while switching to SQL Server Database
• Ability to restore SQL Server database from MDF file in an efficient way
• Application supports both ASCII as well as Unicode XML data type

Summing Up

Every SQL DBA faces challenges while doing their work. Among all, SQL Server Error 5123 Access Denied is one of the most common errors. Thus, to resolve this error, in this blog, we have discussed all manual ways in an absolute and efficient way.

Apart from that, we have covered the possible reason behind the occurrence of SQL Server attach database error 5123. Also, we have introduced an alternate solution named as SQL Recovery tool, which helps to fix corrupt SQL server database without any risk or data loss.

FAQs

Q-1. What is error code 512 in SQL Server?

Ans: When a user tries to run a query that returns more than one value despite the fact that it’s not allowed at that place, such an error occurs. This error is different from the  SQL error 5123.

Q-2. How to remove error in SQL Server?

Ans: This varies on the factor that which error users are experiencing. To remove error in SQL Server, users must understand the root cause first. Then using the SSMS or automated solution, users can fix the error.

Q-3. How do I fix SQL Server error 5123?

Ans: Download the recovery tool for SQL & follow the simple steps:

• Add MDF File
• Select Scan Mode
• Enter Destination
• Hit the Export Button

Q-4. How do I fix SQL connection error?

Ans: There are following steps to fix SQL connection error, simply follow these steps.

• Verify SQL Server Instance is in Running State
• Verify Running of SQL Server Browser Service
• Verify SQL Server Name in Connection String
• Verify the Client Machine’s Aliases as Well
• Verify the Configuration of the Firewall
• Enabled Protocol Verification in Server
• Test Local & Remote Connection
• Execute the TCP/IP Connectivity

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.

Figure 2 – Select Security in SQL Database Properties Window

• 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.

Figure 3 – Select Full Control Permission

• 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.

Figure 4 – Connect to SQL Server Instance

• 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’.

Figure 5 – Select Database To Attach in SQL Server

• 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:DataFilesMyDatabase_Data.mdf’,@filename2 = N’E:LogFilesMyDatabase_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.

Модернизация и обновление нашего ЦОД почти закончено, осталось дождаться корзины резервного копирования и перенастроить бэкапы на новую корзину, но это уже не срочно, поэтому пришло время приступить к поэтапному обновлению инфраструктурных и продакшин серверов. На минувшей неделе, было принято решение перенести и консолидировать все на одном, уже 2016 сервере (Windows 2016+SQL 2016), нашу систему мониторинга «SolarWinds».

Необходимо было перекинуть базу с SQL2008 на SQL2016 но входе переноса что-то пошло не так, забэкапленная база никак не хотела разворачиваться   на SQL2016… Пришлось «плясать с бубном» пытаясь разными методами решить вопрос, но в определенный момент перестала стартовать и рабочая база на старом сервере SQL2008, восстановили ее из бэкапа, но при попытке Примонтировать восстановленную базу стала выскакивать ошибка 5123,

это было странно, так как вроде никаких изменений не было и все пути и папки были прежними.

Оказалось, ларчик открывался достаточно просто. Если в MS SQL Server Management Studio я захожу не под локальной учеткой SA, а под доменной compd.khlebalin, то на mdf и ldf  файлы восстановленной базы тоже должны быть полные права у этой учетной записи. Можно каждому файлу раздать права а можно дать себе право на всю папку с файлами и наследовать разрешения ко всем файлам этой папки:

После чего вновь монтируем восстановленную базу, и как видим, более ошибка не возникает:

Далее продолжим наши изыскания в переносе…

Всем хорошей работы!!!

04.07.2017 —

Posted by |
ms sql 2000/2005/2008/2012/2014