SQL Server attaches a database and reports error 5123.

SQL Server attaches a database and reports error 5123.

Recently, my computer has been upgraded from WIN7 to WIN10. After installing common software and SQL SERVER, attach some test databases and the following error is reported:

Figure-1: An error is reported when an SSMS database file is attached.

 

Try a script and report the following error:

Figure-2: An error is reported when a database file is attached using an SQL script.

 

According to the error message, it is estimated that the operated user has insufficient permission to access the file. However, the same error is reported for both windows authenticated logon and sa logon. It's strange that my computer uses the administrator privilege. Check the database file permissions and find that:

Figure-3: security attributes of database files

 

Even the Administrator has no permission to read this file! Click the "advanced" option:

Figure-4: security attributes of database files-advanced

 

At this time, I was impressed that the owner of this file was another administrator user (a user must be specified during WIN10 installation). I am used to using the administrator user, after the installation, I deleted the user created during the installation and changed it to "administrator. Modify the file owner to administrator and start the user in SQL Server Configuration Manager:

Figuer-5: SQL Server Configuration Manager-Log On

 

Restart the instance and use windows authentication to append the database. Then, you can test and append the database with sa. Although the problem has been solved, for SQL server, permission security depends on the security configuration of windows for a large program. In this respect, I am not very familiar with this problem, so I searched the internet for the same problem. This article can be referenced, and I hope my experience can help others.

This article permanently updates the link address: