Correct solution to error 5123 in SQL Server attached database

Because I have a database tutorial based on SQL Server 2005, the sample database used in it is AdventureWorks for SQL Server 2005, and my machine is installed with SQL Server 2008, and the sample database is AdventureWorks for SQL Server 2008. At first, I thought that the database structures of the sample databases AdventureWorks for SQL Server 2005 and AdventureWorks for SQL Server 2008 should be similar. However, during the exercise, I found that the structures of many tables in the two databases were still very different. So I decided to download the sample database AdventureWorks for SQL Server 2005 from the Microsoft Download Center and attach it to SQL Server 2008 for smooth practice. I logged in to the instance SQLSERVER2008 using the super administrator account "sa" of SQL Server 2008:

When attaching the sample database AdventureWorks for SQL Server 2005, the following error popped up:

Look carefully Take a look at the main message "CREATE FILE encountered operating system error 5 (Access Denied.) when trying to open or create a physical file...". At a glance, you can tell that the operating permissions for the data file to be attached are insufficient. According to general thinking habits, we will grant sufficient operation permissions to files with insufficient operation permissions. For example, some netizens said, "Grant Everyone permissions to the data files to be attached and the corresponding log files." The authorization process is shown in the following three screenshots (note that both data files and log files must be authorized):

(picture 1: Authorize the data file)

(Figure 2: After the data file is authorized)

(Figure 3: After the log file is authorized)

Grant the data files and log files to be attached to Everyone respectively After obtaining [Read and Execute] and [Read] permissions, I tried to attach the database again in SQL Server 2008 and found that the attachment was successful! Is the problem solved like this? Is this the right thing to do? " If in the actual database management process, we enlarge the permissions of data files and log files to Everyone, it is definitely wrong. Because the security of the database will be greatly compromised, although only [Read and Execute] and [Read] permissions are granted to Everyone, there is still a risk of data leakage.

We should ensure that data files have minimal access rights when they can be accessed normally. We previously authorized it to Everyone, so all users or accounts can operate the corresponding files, which is definitely not safe. So how can you grant minimal access? Think about it, we use SQL Server 2008 to attach the corresponding data files and report an "Access Denied" error, that is, insufficient permissions. In other words, currently SQL Server 2008 does not have permission to access these files. We right-click the file and go to the file properties to check the file permissions, as shown in the figure below:

(the original permissions of the corresponding data file)

We found that there are only two groups or users: SYSTEM and xrm Only those with the right to operate this data file. SYSTEM is a user group, that is, the [Local System] group, and xrm is an administrator user, as shown in the figure:

(xrm user information)

The SYSTEM user group and the xrm administrator user are both You have permission to operate this data file and log file, but after logging in to the instance with the super administrator SA connection of SQL Server 2008, SQL Server does not have permission to access this data file. In other words, after logging in to the instance with the super administrator SA connection of SQL Server 2008, the logged-in identity is not in the SYSTEM user group, nor is it the administrator xrm. What could that be?

                                                                                                                                                                                                      Let’s check the relevant information of the current instance service of SQL Server 2008. Open the Sql Server Configuration Manager (i.e. SQL Server Configuration Manager) to check the relevant information of the currently connected instance service, as shown in the figure below:

(Related information about the current instance service)

It was found that the login identity of the current instance SQLSERVER2008 is "NT AUTHORITYLocalService", which is the [local service] authorized by the operating system, and the local service is also a user group. In other words, if we only grant permissions to the [Local Service] user group (instead of Everyone), we should be able to use the sa account to attach the database in SQL Server 2008. To this end, delete the permissions just granted to the corresponding data files and log files Everyone, then grant the permissions to the corresponding data files and log files to the LocalService user group, try to attach the corresponding database again, and find that the attachment can indeed be successful! Needless to say, granting operating system authorization to the [Local Service] user group is definitely much safer than granting it to Everyone.

In the method mentioned above, we have changed the original permission scope of the data file (the original permission scope is only SYSTEM, the [Local System] user group and the xrm system administrator). A better way is not to change the permission scope of the data file. The instance of SQL Server 2008 that is still connected and logged in as SA can also access the corresponding data file. To achieve this goal, we only need to change the login identity of the corresponding instance to the SYSTEM [Local System] user group. SYSTEM is also a user group within the permissions of the corresponding data file, and the SQL Server instance runs as the local system. Security will be higher. We can modify the login identity of the corresponding SQL Server instance to [Local System] in the SQL Server Configuration Manager, as shown in the following figure:

(modify the login identity of the instance)

(The login identity of the instance changes to LocalSystem)

Then restart the corresponding instance service, reconnect and log in to the corresponding instance of SQL Server 2008 as SA and try to attach the database. The database can also be attached successfully! ! !

In fact, if you do not specifically need to connect and log in to the corresponding instance of SQL Server 2008 as an SA to attach the corresponding database, then when connecting to the corresponding instance of SQL Server 2008, select [Windows Authentication] for authentication. You can attach the database by making other modifications as mentioned above. The reason is: [Windows Authentication] uses the permissions of the user of the current operating system, and the permissions are generally large enough. In addition, the operations that can be done on the instance server in [SQL Server Configuration Manager] can also be done on Windows [Service]

More correct solutions to error 5123 in SQL Server attached database For method-related articles, please pay attention to the PHP Chinese website!