Solution to SQL2005 Additional Database Error (Error Number: 5123)


[Error Tips]
Data cannot be retrieved for this request. (Microsoft.SqlServer.SmoEnum) Exceptions occur when other information executes Transact-Sql statements or batches. (Microsoft.SqlServer.ConnectionInfo) When trying to open or create a physical file “d:\Data\\Data***.mdf”, Create file encountered an operating system error 5 (denial of access).

[Background of the problem]
Database Version: SQL Server 2005 Enterprise Version
Operating System Version: Windows Server 2003 Enterprise Version
Error-triggered description: After the database is separated, the data hard disk is transferred from one host to another (both hosts have the same operating system and database version). When the result is attached to the database, the above error is prompted.

[Problem analysis]
Obviously, it’s a problem caused by inadequate file operation privileges.

Add appropriate users to the folder attribute security where the database files are located and grant full operation privilegesThe problem can be solved (as all the information on the Internet says). What users do you add permissions to? For example: Administrators user group, NETWORK SERVICE, MSSQL User or Everyone, which specific use, see which account your SQL service is started, if not clear or lazy, you can directly add Everyone users. I didn’t know why I wanted to upload dozens of Kb pictures from the last screenshot.

Maybe you added user and operation rights to the folder where the data file is located through the above solution, but the problem is still the same (which is where I struggle, as the Internet says, why not?!). Finally, my solution is:Set user and operation privileges for each database file (including mdf, ldf, ndf). (The operation method is the same as adding operation privileges to folders, which are security options in right-click properties.)。 The problem was finally thoroughly solved. The reason should be that the permissions of folders are not automatically inherited to the files inside.