SQL Server attaches Database Error 5123 correct solution

SQL Server attaches Database Error 5123 correct solution

Because I have a database tutorial based on SQL Server 2005, the sample database used in the tutorial is AdventureWorks for SQL Server 2005, and my computer is equipped with SQL Server 2008, the sample database is AdventureWorks for SQL Server 2008. At first I thought that the structure of the sample database AdventureWorks for SQL Server 2005 and AdventureWorks for SQL Server 2008 should be similar, but during the exercise, I found that the structures of many tables in two databases are different. So I decided to go to the Microsoft download center and attach the sample database AdventureWorks for SQL Server 2005 to SQL Server 2008 for smooth exercises. I log on to the instance SQLSERVER2008 using the super Administrator Account "sa" of SQL Server 2008:

This error is prompted when you append the sample database AdventureWorks for SQL Server 2005:

After carefully reading the main information, "create file encountered an operating system error 5 (Access denied) when trying to open or CREATE a physical FILE .)" At first glance, we know that the Operation permission on the data file to be attached is insufficient. In general, we grant sufficient operation permissions to files with insufficient operation permissions. For example, some netizens say that "granting the Everyone permission to the data file to be attached and the corresponding log file" is shown in the following three authorization procedures (note that both the data file and log file must be authorized ):

(Figure 1: Authorizing data files)

(Figure 2: Data File authorization)

(Figure 3: Log File authorization)

Grant Everyone [read and execute] and [read] permissions to the data files and log files to be appended, and then re-try to attach the database in SQL Server 2008, the append is successful! Is the problem solved in this way? Is this right? If we enlarge the permissions of data files and log files to Everyone in the real database management process, it is definitely wrong. In this way, the security of the database will be greatly reduced. Although Everyone is granted only the [read and execution] and [read] permissions, there is still a risk of data leakage.

We should ensure that the data file has the minimum access permission when it can be accessed normally. If we previously authorized Everyone, all users or accounts can operate the corresponding files, which is definitely not safe. How can we grant the minimum access permission? Think about it. We use SQL Server 2008 to append the corresponding data files and report an error where "access is denied". In other words, SQL Server 2008 does not have the permission to access these files. Right-click the file and choose file properties to view the file permissions, as shown in:

(Original permissions of the data file)

We found that only the SYSTEM and xrm groups or Users have the right to operate the data file. SYSTEM is a user group, that is, the local SYSTEM group, and xrm is an administrator user, as shown in:

(Xrm user information)

Both the SYSTEM user group and the xrm administrator user have the permission to operate on this data file and log file. After connecting to the instance with the SQL Server 2008 Super administrator SA, SQL Server does not have the permission to access this data file. In other words, after connecting to the instance as the super administrator SA of SQL Server 2008, the logon identity is not in the SYSTEM user group or xrm. What would it be?

Check the information about the instance service of SQL Server 2008. Open SQL Server Configuration Manager to view the information about the instance service currently connected, as shown in:

(Information about the current instance service)

The logon 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 authorize the user group [local service] (rather than Everyone), we can also append the database with the sa account in SQL Server 2008. To this end, the permissions granted to the corresponding data file and log file Everyone are deleted, and then the permissions granted to the corresponding data file and log file of the LocalService user group are re-attached to the corresponding database, the attachment was successful! Needless to say, the local service user group authorized to the operating system must be much safer than Everyone.

In the method mentioned above, we changed the original permission range of the data file (the original permission range is only SYSTEM, that is, the local SYSTEM user group and the xrm SYSTEM administrator ). The better way is not to change the permission range of the data file. You can still connect to the SQL Server 2008 instance as a SA to 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 permission range of the corresponding data file, in addition, the SQL Server instance runs as a local system with higher security. In the SQL Server Configuration Manager, we can change the logon identity of the corresponding SQL Server instance to Local System, as shown in the following figure:

(Modify the login identity of the Instance)

(The instance login identity becomes LocalSystem)

Restart the corresponding instance service, log on to the corresponding instance of SQL Server 2008 as a SA, and try to append the database. Similarly, you can successfully append the database !!!

In fact, if you do not need to connect to the corresponding instance of SQL Server 2008 as a SA to attach the corresponding database, when you connect to the corresponding instance of SQL Server 2008, for authentication, select Windows authentication. You can attach the database without making any changes described earlier. The reason is: [Windows Authentication] uses the permissions of users in the current operating system. The permissions are generally large enough. In addition, you can perform operations on the instance Server in [SQL Server Configuration Manager] On Windows [services.

This article permanently updates the link address: