For a Windows application or service, it is often necessary for either the user or service startup account to have full control to its folders and files. In most cases, this is transparent to the user. It is generally being taken care of by various delegation mechanisms behind the scene. However, this can be a problem during application upgrade or migration, when one rearranges disk drive letters or mount point names, or moves things from one server to another. To resolve this, one could manually go to each file and folder property page through Windows Explorer, then the Security tab, then the Edit… button, then the Add… button, etc. This method isn’t too bad if only a handful of files and folders are involved. But it is easy to see that manual method gets old really quickly, is error prone, and does not scale.

For example, say you are doing SQL Server migration for a data warehouse instance, where there are multiple mount points and hundred of database files due to filegroup usage and table partitions. During the migration process, you’ll move them to a new location and attach those database files. To ensure SQL Server service SID has proper permission is no small task and we need an automated way.

Given this is Windows, I started searching around for a PowerShell solution. You’d think this is a common problem, and therefore it shouldn’t be too difficult to find a solution. But that’s not the case so I wrote my own. Have fun with it!

Notes:

• Make sure you start PowerShell with “Start as Administrator”. This is to get around the issue that the creator and permission granter are different identities;
• The first thing the script does is to give ownership to the Administrators group. During execution, you’ll see output like:
  SUCCESS: The file (or folder): “x:\MountPoints\A1″ now owned by the administrators group.
  that is to be expected.
• The script’s execution is recursive, meaning all sub-folders and files are covered.

Without further ado, here is the PowerShell function:

function grantFullPermission ($folderpath, $userAccount) {
    Invoke-Expression "takeown /f $folderpath /r /a /d Y"
    $aclWork = (Get-Item $folderPath).GetAccessControl("Access")
    $ssaf = New-Object system.Security.AccessControl.FileSystemAccessRule($userAccount, "FullControl", "ContainerInherit, ObjectInherit", "None", "Allow")
    $aclWork.SetAccessrule($ssaf)
    Set-ACL $folderPath $aclWork }

As an example, to grant proper permission to 3 folders listd in the code, you can run the following script as administrator:

function grantFullPermission ($folderpath, $userAccount) {
    Invoke-Expression "takeown /f $folderpath /r /a /d Y"
    $aclWork = (Get-Item $folderPath).GetAccessControl("Access")
    $ssaf = New-Object system.Security.AccessControl.FileSystemAccessRule($userAccount, "FullControl", "ContainerInherit, ObjectInherit", "None", "Allow")
    $aclWork.SetAccessrule($ssaf)
    Set-ACL $folderPath $aclWork }

$folders = @("x:\MountPoints\A1", "x:\MountPoints\A2\", "x:\MountPoints\B1")

$folders | foreach {
    grantFullPermission $_ 'NT SERVICE\MSSQLSERVER' }

You could also make this a short program that takes two parameters: a folder and an account. Remember to quote the parameters if they have space character.

param(
[parameter(Mandatory=$true)]$DirectoryPath,
[parameter(Mandatory=$true)]$AccountName)

function grantFullPermission ($folderpath, $userAccount) {
    Invoke-Expression "takeown /f $folderpath /r /a /d Y"
    $aclWork = (Get-Item $folderPath).GetAccessControl("Access")
    $ssaf = New-Object system.Security.AccessControl.FileSystemAccessRule($userAccount, "FullControl", "ContainerInherit, ObjectInherit", "None", "Allow")
    $aclWork.SetAccessrule($ssaf)
    Set-ACL $folderPath $aclWork }

grantFullPermission $DirectoryPath $AccountName