SQL Server database error 5123 solution

Because I have a database tutorial based on SQL Server 2005, and the sample database used in it is AdventureWorks for SQL Server 2005. However, my computer 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 database AdventureWorks for SQL Server 2005 and AdventureWorks for SQL Server 2008 should be similar, but during the practice, I found that the structures of many tables in the two databases are 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 so that I could carry out the practice smoothly.

I log in to the SQLSERVER2008 instance as the SQL Server 2008 super administrator account "sa":

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

After carefully looking at the main information "CREATE FILE encountered operating system error 5 (Access denied) while trying to open or create a physical file...", I knew at a glance that the operation permissions for the data file to be attached were insufficient. According to general thinking habits, we will grant sufficient operating permissions to files that do not have sufficient operating 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):

(Figure 1: Authorization data file)

(Figure 2: Data file after authorization)

(Figure 3: Log file after authorization)

After granting Everyone [Read and Execute] and [Read] permissions to the data files and log files to be attached, I tried to attach the database again in SQL Server 2008 and found that it could be attached successfully! Is this how the problem is solved? Is this the right way to do it? If, in the actual database management process, we expand the permissions of data files and log files to Everyone, it is definitely a wrong approach.

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 while being able to access them normally. We previously authorized Everyone, so all users or accounts can operate the corresponding files, which is definitely not safe.

So how can we grant minimal access rights? Think about it, we use SQL Server 2008 to attach the corresponding data files, and report an "Access Denied" error, which means insufficient permissions. In other words, the current 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 below:

(Original permissions of the corresponding data file)

We found that only the SYSTEM and xrm groups or users have the authority to operate this data file. SYSTEM is a user group, namely the [Local System] group, and xrm is an administrator user, as shown in the figure:

(xrm user's information)

The SYSTEM user group and the administrator user xrm both have the authority to operate this data file and log file. However, after logging in to the instance as the super administrator SA of SQL Server 2008, SQL Server does not have the authority to access this data file. In other words, after logging in to the instance as the SQL Server 2008 super administrator SA, the logged-in identity is not in the SYSTEM user group, nor is it the administrator xrm.

What would that be? We can find out by checking the relevant information of the current SQL Server 2008 instance service. Open Sql Server Configuration Manager (i.e. SQL Server Configuration Manager) to check the relevant information of the instance service currently connected, as shown in the following figure:

(Related information about the current instance service)

It is found that the login identity of the current instance SQLSERVER2008 is "NT AUTHORITY\LocalService", which is the [Local Service] authorized by the operating system. 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 attach a database using the sa account in SQL Server 2008.

To this end, delete the permissions that have just been granted to Everyone on the corresponding data files and log files, then grant permissions to the LocalService user group on the corresponding data files and log files, and try to attach the corresponding database again. You will find that it can indeed be attached successfully! Needless to say, granting operating system authorization to the [Local Service] user group is definitely much safer than granting it to Everyone.

In the methods mentioned above, we have changed the original permission scope of the data file (the original permission scope only includes SYSTEM, the [local system] user group, and xrm, the system administrator).

A better way is not to change the permission scope of the data file. You can still log in to the SQL Server 2008 instance as SA to access the corresponding data files.

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 permission range of the corresponding data file, and the SQL Server instance runs as a local system, which will have higher security. We can change the login identity of the corresponding SQL Server instance to [Local System] in SQL Server Configuration Manager, as shown in the following figure:

(Change the login identity of the instance)

(The instance's login identity becomes 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 need to connect to the corresponding instance of SQL Server 2008 as SA to attach the corresponding database, then when connecting to the corresponding instance of SQL Server 2008, select [Windows Authentication] for authentication, and you can attach the database without making other changes as described in the previous article. The reason is that [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 performed on the instance service in [SQL Server Configuration Manager] can also be performed on the [Service] in Windows.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.