Ошибка SQL Server 5123 при подключении базы данных
У меня есть две базы данных. Оба находятся в одной папке. Оба, насколько я могу судить, имеют одинаковые права собственности и права доступа. Я могу прикрепить одно, но не другое — я получаю ошибку 5123.
Для обоих файлов.
- Владелец MSSQLSERVER
MSSQLSERVER — Полный контроль
ПРАВА ВЛАДЕЛЬЦА — Полный контроль
Администраторы — Полный контроль
Я рассмотрел другой вопрос, упомянутый ниже, но не смог заставить работать ни одно из предложений, учитывая мои ограничения, перечисленные выше. Я опубликовал то, что, наконец, работает ниже.
7 ответов
Добавьте своего пользователя в разрешения:
Все файлы на платформе ОС имеют права доступа. Помните, что для оптимальной безопасности DENY всегда отменяет любые разрешенные разрешения. База данных SQL Server имеет физические файлы (.MDF, .NDF и.LDF). Эти файлы, как и любые другие файлы на сервере, имеют права доступа.
В вашем сценарии ошибки SQL Server 5123 вы пытаетесь присоединить файл базы данных к экземпляру, где ваши учетные данные не имеют необходимых прав доступа к файлу. Хитрость здесь в том, что тот, кто изначально отключил файл базы данных, автоматически назначается владельцем и никем другим.
Чтобы это исправить, вы должны предоставить права доступа R/W или FULL учетной записи, пытающейся прикрепить файл. Примечание. Если для базы данных существует несколько файлов, которые, скорее всего, будут, вам необходимо убедиться, что свойства безопасности каждого файла установлены правильно для пользователя, выполняющего ATTACH.
SQL Server Error 5123 When Attaching Database
I have two databases. Both are in the same folder. Both have, as far as I can tell, the exact same ownership and permissions. I can attach one but not the other — I get an error 5123.
- Owner is MSSQLSERVER
MSSQLSERVER — Full control
OWNER RIGHTS — Full control
Administrators — Full control
I reviewed the other question referenced below but could not get any of the suggestions to work given my constraints listed above. I posted what finally worked below.
5 Answers 5
Add your User to Permissions:
All files on the OS platform are subject to access permissions. Remember that for optimal security, DENY always overrides any permissions allowed. The SQL Server database has physical files(.MDF, .NDF, and .LDF) These files just like any other files on the server is subject to access permissions.
In your scenario of SQL Server error 5123, you are attempting to attach a database file to the instance where your credentials do not have the right privileges on the file. The trick here though is that whoever initially detached the database file is automatically set as the owner and no one else.
In order to fix this, you have to give the R/W or FULL control rights to the account attempting to attach the file. Note: If there are multiple files for the database which most likely would be, you need to make sure each file security properties is set properly for the user performing the ATTACH.
Blogging About SQL Tips & Tricks
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:
- This error can be occur if you have previously detached the database with different logins and trying to attach the database with different logins.
- 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
- Right click on the MDF file, which you want to attach.
- Select properties to check the permissions of the MDF file.
- 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
- Log in to the SSMS using valid server’s name and Windows 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.
- A new Attach Databases Window will be open, now click on the ‘Add‘ button.
- Select the database, which you want to attach.
- 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.
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.
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.
Fix Error 5123 SQL Server in 5 Steps
- Launch Tool and Add MDF Files.
- Select Quick/Advance Scan Mode.
- Enter Destination Location for Files.
- Select Schema or Schema with Data.
- Click Export to Fix Error 5123 SQL Server.
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.
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.
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.
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
Exclusive Offers & Deals, Grab it Now!
By Ashwani Tiwari
Being a Chief Technical Analyst, I am aware of the technicalities faced by the user while working with multiple technologies. So, through my blogs and articles, I love to help all the users who face various challenges while dealing with technology.