Moving SQL Server Database Files to New Location - Why permission error?

Question

I was trying to move a database (.mdf and .ldf files) to a different directory on the same server hosting the SQL server. I followed these steps found in this Q&A:

1. ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

2. ALTER DATABASE MyDB SET OFFLINE

3. ALTER DATABASE MyDB MODIFY FILE (Name = MyDB, Filename = 'N:\DATA\MyDB.MDF')

4. ALTER DATABASE MyDB SET ONLINE

5. ALTER DATABASE MyDB SET MULTI_USER

When I got to Step4, I got "Access Denied". Unfortunately, my maintenance windows were very short and no time to troubleshoot. So I decided to drop the database and restored it using the WITH MOVE clause to place the DB files in the right directory. No issue.

My question is why did I get "Access Denied"? I didn't change MSSQL service account. It's the same account that ran the RESTORE.

Answer 1

The ALTER DATABASE MyDB MODIFY FILE (Name = MyDB, Filename = 'N:\DATA\MyDB.MDF') statement doesn't actually move the files. It merely moves the definition of where the files reside. You'd need to actually move the files manually before continuuing to step 4.

When you move the files prior to step 4, you must ensure the target directory has the correct permissions for the SQL Serve service account. This document details the requirements.

Answer 2

A key point here is whether you moved or copied the database files. And if you "moved" the files between two different disks you actually copied them (there no such thing as moving a file between two disks even if commands/GUIs let you believe so).

Moving a file retains the owner and privileges on the file.

Copying on the same disk retains permissions but the owner of the new file will be the persons who did the copy.

Copying or moving a file to a different disk result in the person who does the copy/move becomes the owner of the new file. The old permissions is not retained.

What you see if common if you "move" a file between disks, and subsequently SQL Server won't have privileges on the new files. I.e., you need to set suitable permissions on the files to the SQL Server service account.