Bulk Modify SQL Server Database Disk File Permissions

The company adjusted its security policies. Ultimately, ‘Mechanical Mini’ was relocated back home as a backup server, along with a full system reinstallation. Ubuntu switched to Windows Server; due to an irregular activation method – used at home – it seemed like it wouldn’t be activated, and that was fine. An unconventional activation triggered Microsoft’s detection (running normally for half a month), the server would automatically shut down after running for one hour. After reviewing the system logs, it was discovered that this was due to using a pirated version.

There wasn’t much else to do, so the system was reinstalled again, and SQL Server also needed to be reinstalled – it’s always a bit of a pain each time. File permission control is very strict, making it impossible to attach the database normally.

Error Message

After the system reinstallation, SqlServer may encounter error 5120, an operating system access denied error, when attaching a database.

Processing Script

Referencing the previous link: Batch Update Local Git Repository, it’s that familiar script all over again – modified to, we iterate through folders while modifying file permissions. Currently used with full editing permissions.

Most tutorials online have you manually modify files. They only need to change a few files each time? I always have to process batches of files; doing everything manually is going to drive me crazy.

$currentUserName = [System.Security.Principal.WindowsIdentity]::GetCurrent().Name
[Console]::OutputEncoding = [System.Text.Encoding]::UTF8
$rootDirectory = "D:\data\2013_RujiaInfo"

Get-ChildItem -Path $rootDirectory -Recurse | ForEach-Object {
    $itemPath = $_.FullName

    if ($_ -is [System.IO.DirectoryInfo]) {
        $icaclsResult = icacls $itemPath /setowner "$currentUserName" 2>&1
        if ($LASTEXITCODE -eq 0) {
            Write-Host "Changed the owner of folder $itemPath to $currentUserName"
            # Grant current user write permissions
            Invoke-Expression "icacls `"$itemPath`" /grant `"$($currentUserName):(OI)(CI)F`""
            Write-Host "Granted $currentUserName editing permissions for the folder"
        } else {
            Write-Host "Unable to change the owner of folder $itemPath. Error message: $icaclsResult"
        }
    } else {
        $takeownResult = icacls $itemPath /setowner "$currentUserName" 2>&1
        if ($LASTEXITCODE -eq 0) {
            # Grant current user write permissions
            Invoke-Expression "icacls `"$itemPath`" /grant `"$($currentUserName):(F)`""
            Write-Host "Granted $currentUserName editing permissions for the file"
        } else {
            Write-Host "Unable to change the owner of file $itemPath. Error message: $takeownResult"
        }
    }
}