Can't add file permissions need to relocate SQL Server 2012 files to separate drive

Question

After installing a new SQL Server 2012 Instance, I wish to move the database files to a separate drive. When I perform File->Properties on the target location and then click Security->Edit->Add->Advanced, select Location=MachineName, and click find, I see only the form related to analysis services:

SQLServerMSASUser$MachineName$InstanceName

I think the permission I really need is this one which I can see exists in the default C: drive location:

MSSQL$InstanceName

When I attempt to just enter it directly it is unrecognized. If I set the directory to "Everyone" and then move the database there (using a restore) it works and shows the above permission on the mdf file. How can I set that permission on the parent directory when it isn't showing up as an available entry in the list. Thanks!

Answer 1

Looking at the install logs, the account MSSQL$InstanceName is shorthand for:

NT Service\MSSQL$InstanceName

which can be entered directly. I still don't know why these don't show up in the list though.

Answer 2

Here's two much easier ways if you can't determine the correct user permissions required (provided you have admin permissions)...

Method 1 - Disk replacement
(i.e. drive letter of SQL Server database files will ultimately be the same)

Especially useful if you are replacing a secondary hard drive where the SQL Server database files reside (e.g. for a bigger one). Right-click the database(s) in Microsoft SQL Server Management Studio (SSMS), Tasks, Take offline.

Then simply copy the files and preserve their ownership:

• go to a command prompt (if on Windows NT 6.x, e.g. Vista / 7 / 2008, remember to right click on Command Prompt and Run as administrator)

• then use:

  xcopy D:\path\sql1 E:\path\sql2 /E /F /I /V /K /O /X
  

where:

• /E - copy directories and subdirectories, including empty ones
• /F - displays full source and destination files when copying
• /I - if destination does not exist and copying more than one file, assumes destination is a directory
• /V - verifies each new file
• /K - preserves attributes, including read-only
• /O - copy file ownership and ACL permissions
• /X - copy file audit settings (implies /O)

you may also want, depending on what files you are migrating:

• /C - continues copying even if errors occur
• /G - allows copying of encrypted files to a location that does not support encryption
• /H - copies hidden and system files

Replace the drive and make sure the drive letter is the same as before. Finally right-click the databases in SSMS, Tasks, Bring online.

Method 2 - Moving the files to another disk or partition
(i.e. drive letter of SQL Server database files will change)

Useful when you want to free up space on a drive/partition (such as on your precious SSD disk so you can put the files on a secondary standard platter disk!).

Right-click the database(s) you want to move in SSMS, Tasks, Detach. Navigate to the files, which are typically at something like C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA and called MyDatabase.mdf and multiple copies of MyDatabase_1.ndf. Copy them to the new location (don't worry about their permissions).

Then open SSMS as an administrator otherwise you will end up with a CREATE FILE error with something like Access is denied in the next step.

Right-click databases, Attach, Add, browse to the newly located MyDatabase.mdf.

Voilà! If you right-click the files and check their permissions before and after attaching in SSMS you'll notice that the permissions have been magically corrected!

Answer 3

Here's the relevant MSDN article: Configure File System Permissions for Database Engine Access