SQL server attached database has the correct workaround for error 5123

This error pops up when attaching the sample database AdventureWorks for SQL Server 2005: Take a closer look at the main message "Try to open or create a physical file ... , the CREATE file encounters an operating system error 5 (Access denied.     ), you should know that you should not have enough permissions on the data file you want to attach. As a general rule of mind, we will grant sufficient permissions to the files that do not have enough permissions to operate. For example, a netizen said, "Give everyone permission to the data file and the corresponding log file to be attached", the authorization process is as follows three (note that both the data file and the log file must be authorized): (Figure 1: Authorization data file)
(Figure 2: After the data file is authorized)

(Figure 3: After the log file is authorized)

Once you have given everyone "read and execute" and "read" permissions to the data files and log files that you want to attach, retry attaching the database in SQL Server 2008 and find that you can attach the success! Is the problem solved?     Is this the right way to do it? If in the real database management process, we have the data file, log file permissions to enlarge to everyone, that is certainly not the practice. Because of this, the security of the database will be compromised, although everyone is given only read and execute, read permissions, but this still has the risk of leaking data.

      We should ensure that the data files have minimal access in the event that they are properly accessed. We previously authorized everyone, that all users or accounts can operate the corresponding files, which is certainly not safe. So how do you grant the least access right? Think about it, we use SQL Server 2008 to attach the corresponding data file, to report "Access denied" is not enough permissions error, in other words, the current SQL Server 2008 does not have access to these files. We right-click the file to see the file's permissions in the file properties, as shown in:

  (original permissions for the corresponding data file)         we found that only two groups or users of system and XRM have permission to manipulate this data file. System is a user group, which is the "Local System" group, and XRM is an administrator user,:  (xrm user's information)        The System user group and XRM this administrator user has permission to manipulate this data file and log files, and SQL Server does not have permission to access this data file after logging on to the instance with the Super Administrator SA connection to SQL server 2008. In other words, after logging on to an instance with the Super Administrator sa of SQL server 2008, the login is not in the System Users group, nor is it the XRM administrator. What would that be?           Let's take a look at the information about the current instance service for SQL Server 2008. Open SQL server  The configuration manager  (that is, SQL Server Config Manager) looks at information about the instance service currently connected to, as shown in:  (information about the current instance service)          discovers that the current instance SQLSERVER2008 login as "NT AUTHORITY\LocalService", 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 the "Local Service" user group permission (not everyone), you should also be able to attach the database in the account of SA in SQL Server 2008. To do this, just grant the appropriate data files and log files to everyone's permissions are removed, and then grant the LocalService user group the appropriate data files and log file permissions, re-attempt to attach the corresponding database, found that can indeed attach success! Needless to say, the local services user group granted the operating system authorization is much more secure than granting everyone.      of the methods mentioned above, we have changed the original permission scope of the data file (the original permission range is only system, the "Local System" user group and XRM)  . And the better way is not to change the data filePermission scope, an instance of SQL Server 2008 that is still connected as an SA is also able to access the corresponding data file. To achieve this, we only need to change the login identity of the corresponding instance to the system Local System user group, the system is also the user group within the permissions of the corresponding data file, and the SQL Server instance runs as the local system, and the security will be higher. We can modify the login identity of the corresponding SQL Server instance to local system in SQL Server Configuration Manager, as shown in the following figure:  (modify the logon identity of the instance)      (The instance's login identity becomes LocalSystem)       then restart the corresponding instance service, log on to the corresponding instance of SQL Server 2008 again as SA, and attempt to attach the database,  The same can be successfully attached to the database!!!   
In fact, if you are not specifically connecting to the corresponding instance of SQL Server 2008 with the SA identity to attach the corresponding database, authentication selects Windows authentication when connecting to the appropriate instance of SQL Server 2008. Do not do the other changes described in the previous article can be attached to the database, because: "Windows authentication" with the current operating system of the user's permissions, the permissions are generally large enough.  In addition, the actions that can be done in SQL Server Configuration Manager for instance services are also available on the Windows service. Tags: SQL Server attached database, error 5123, Microsoft SQL Server error 5123

SQL server attached database has the correct workaround for error 5123