Solving the access denied problem of SQL Server Express
Today I installed SQL Server Management Studio (Express Edition) onto my computer, and tried to attach a database MDF file on to it. The MDF file was previously created by Visual Studio and was stored in my Documents folder. It can be opened by Visual Studio automatically each time it is launched. I tried to attach it in the SQL Server Management Studio this time, because it is not listed in the list of Databases in the SQL Server Management Studio. However, each time I tried to attach the file in, I receive below error message:
Attach database failed for Server ‘PVGN50789864A\SQLEXPRESS’. (Microsoft.SqlServer.Smo)
Directory lookup for the file “C:\Users\I064383\Documents\SAP\Notes\RobbieSAP.mdf” failed with the operating system error 5(拒绝访问。). (Microsoft SQL Server, Error: 5133)
I didn’t understand the message initially. I tried setting the permission for the MDF and LDF files to allow access by “Network Service”, which is the built-in service account used by SQL Server. However that still doesn’t work. Lateron, I recall that SysInternals Process Monitor might help in finding out the reason. I launched Process Monitor, turned on monitoring of the process “sqlservr.exe”, and retried attaching. After watching the list, I found several ACCESS DENIED records near the end, saying the directory lacks access. Now I understand “Directory lookup for … failed” error message. I allowed Network Service to access the directory, and it attaches the database successfully!
Bravo, another case solved by SysInternals tools!