Solution to error 5123 when attaching database in sql2008

Error loading image

Solution:

1. Directly copy the existing database to the sql installation directory C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA

This way you can attach , I don’t know if I missed an operation when installing the database. I have never encountered it before.

2. On the existing database mdf and ldf files,

right-click---->Properties---->Add---->Advanced---->Search Now---- >Authenticated Users---->OK

------>Full control tick---->OK

Both of the above methods are possible, there may be other methods, please feel free to Let’s look for it again. . . .

The day before yesterday, I redid the system and reinstalled SQLServer2012Express. Today, when attaching an original database, an error was reported. The prompt message was: Unable to retrieve data for this request... An exception occurred while executing Transact-SQL statements or batch processing... ...createfile encountered system error 5 (Access Denied) while trying to open or create the physical file 'd:dataejiaOA.mdf'. (Microsoft SQL Server, Error: 5123).

When I saw this broken prompt, my first impression was that the permissions of the folder seemed not to be in place (my system is 64 and all Win7), using the ancient method: "File->Properties->Security-> Add->Advanced->Find Now->EveryOne->OK->OK->Full Control->Apply" is to add permissions to this folder, but after another operation, it still doesn't work! ! I suddenly felt depressed and asked, "What's going on?" Baidu spent a long time and found some solutions, all of which were inconvenient ways to change permissions. One of the netizens said: Win7 is a very magical thing. Sometimes the operation permissions of the folder will not be changed by the inside. The file is inherited, so you need to check whether the permissions of the file itself also meet the requirements of the operation. I did what this guy said and found that my WIN7 obviously does not have this feature. The permissions of the file are the same as the permissions of its father's folder.

The cause of the problem has never been found, which really gave me a headache for a long time. However, for a person with a tough character like me, we were not scared at all and continued Baidu Baidu Baidu... Finally, I was about to When I was desperate, I found an article and the screenshot is as follows:

The problem described in this document is exactly the same as mine, but the solution proposed is also consistent with my operation method

It seems that I am not the unlucky one. One, but I still found a problem, that is, Windows authentication is used. What will happen if Windows authentication is not used? Exit the SQL2012 manager, log in again using SA, and then perform this additional operation, and it succeeds immediately!

The problem is finally solved, but I am also wondering, why is this? After checking, the original permissions of my database file are as follows:

I have reason to believe that this is the normal permissions of a database file. It would be abnormal if everyone's permissions are added. At the same time, MSSQL$SQLEXPRESS is a permission reserved for SQLServer management for SA login. I logged in to the system with a super-administration account. Obviously it belongs to the super-administration user group, but why does the attached database error occur? If anyone knows, please let me know, I would be very grateful! ! !

Some netizens on Baidu explained this: This is because the startup account of SQL Server (usually system or an operating system administrator) does not have permission to create files in the root directory of drive E (mine is D drive). Right-click the E drive and view the user's permissions in the "Security" tab in the properties. Check who started the account, run services.msc, find the SQL Server service in the service manager, then look at the "Login" tab, select the local user and restart the service.

For more solutions to the error 5123 prompt when attaching the database in sql2008, please pay attention to the PHP Chinese website for related articles!