Because you have a SQL Server 2005-based database tutorial that uses theThe sample database is AdventureWorks for SQL Server 2005, and my machine is loaded with SQL Server 2008, and the sample database is AdventureWorks for SQL Server 2008. At first I thought the sample database AdventureWorks for SQL Server5 and AdventureWorks for SQL Server8 database structure should be similar, but in the course of practice, I found that many of the tables in the two databases have many different structures. So I decided to go to the Microsoft Download Center and AdventureWorks The sample database for SQL Server 2005 and attach it to SQL Server 2008 for a smooth practice. I amlogged on to the instance SQLSERVER2008 with a Super Administrator account "SA" Connection to SQL Server 2008: 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 you want to attach", 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 authorization) (Figure 3: Log file authorization) to append the data files and log files to the individual "read and execute", "read" permission, in SQL Server 2008 to retry attaching the database, found can be attached to the It's done!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 case of normal access. 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 in the file properties to see the permissions of the file, as shown in: (the corresponding data file original permissions) we found that only the system and xrm two groups or users have permission to manipulate this data file. System is a user group, which is the "Local System" group, and XRM is an administrator user, as follows: (XRM user information) system user group and XRM This administrator user has permissions to manipulate this data file and log files, while the Super administrator of SQL Server 2008 After the SA connection is logged on to the instance, SQL Server does not have permission to access the data file. In other words, after logging on to an instance with the Super Administrator SA connection to SQL Server 2008, the logon identity is not in the system Users group, nor is it a XRM administrator. What would that be? Let's take a look at the information about the current instance service for SQL Server 2008 and then openSQL Server 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)The login identity of the current instance SQLSERVER2008 is "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. in the above mentioned method, we have changed the original permission scope of the data file (the original permission scope is only system, the "Local System" user group and XRM). The better way is not to change the permissions of the data file, and still log on as the SA connection to an instance of SQL Server 2008 can also access the corresponding data files. 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. In SQL Server Configuration Manager, we can modify 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 login identity of the instance becomes LocalSystem) then restart the corresponding instance service, re- Connect as SA identity to the corresponding instance of SQL Server 2008 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, then when you connect to the appropriate instance of SQL Server 2008, authentication chooses Windows authentication, not as described in the previous article Other modifications can be added to the database, the reason is: "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.
SQL Server attached database has the correct workaround for error 5123
