So, I had to replace a failing hard drive in my computer at work. It was the D: drive and only had data on it, so no big deal. Put in the new drive, copy the data over, remove the bad drive, rename the new drive to D:, and should be good to go, right? Well, not quite… See, when I had installed SQL Server, I told it to put all of the databases on the D: drive, including master, model, etc. Even though all of those files got copied over to the new D: drive, SQL Server wasn’t happy. I kept getting this error when trying to start up SQL Server:
FCB::Open failed: Could not open file D:\SQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf for file number 1. OS error: 5(Access is denied.).
SQL Server was configured to run under the Network Service account, so I checked the effective permissions for that account on the model.mdf file. They appeared to be sufficient, but not “Full Control”. So I gave Network Service full control on the MSSQL10_50.MSSQLSERVER folder and after that SQL Server started up just fine.
I looked at permissions on some other systems and the account SQL Server is running under does not typically have full control over those files. In fact, it usually seems to have the same, or even more restricted, permissions than what mine had when it wasn’t working. I’m not quite sure what exactly it needs to work, but it’s working now and giving the service full control to that folder probably isn’t too dangerous.