When you change the SQL Server Account or attach Database Files from another computer you may notice the Access Denied error message in SQL Server Management Studio.

To overcome this issue, you may run the following Power Shell script to add the SQL Service User and a userdefined Local Admins Group to each single .mdf, .ldf, .ndf file. Manually setting permissions via Windows Explorer on the parent directory does not change the permissions for each file within.

It might be required to run he Script with elevated Administrative Rights.

function GetFiles($path, [string[]]$exclude) 
{ 
    foreach ($item in Get-ChildItem $path)
    {
        if ($exclude | Where {$item -like $_}) { continue }

        if (Test-Path $item.FullName -PathType Container) 
        {
            $item.FullName
            GetFiles $item.FullName $exclude
        } 
        else 
        { 
            $item.FullName
        }
    } 
}

function SetPermissions($file)
{
    if ($file -like "*.mdf" -or $file -like "*.ndf" -or $file -like "*.ldf" -or $file -like "*.ndf*")
    {
        $acl = Get-Acl $file
        $AccessRule = New-Object System.Security.AccessControl.FileSystemAccessRule("{YOURDOMAIN}\{SQLSERVERDATABASEENGINESERVICEACCOUNT}","FullControl","Allow")
        $acl.SetAccessRule($AccessRule)
        $acl | Set-Acl $file

        $acl = Get-Acl $file
        $AccessRule = New-Object System.Security.AccessControl.FileSystemAccessRule("{LOCALSERVER}\{LOCALSERVERADMINSGROUP}","FullControl","Allow")
        $acl.SetAccessRule($AccessRule)
        $acl | Set-Acl $file
    }
    else 
    {
        $file
    }
}

# First Directory to iterate recursively...
foreach ($file in GetFiles("G:\DWH"))
{
    SetPermissions($file)
}

# Second Directory to iterate recursively...
foreach ($file in GetFiles("P:\DWH"))
{
    SetPermissions($file)
}