Check File System for SQL Server Access across multiple servers

All we need is an easy explanation of the problem, so here it is.

This article describes the File System permissions that are needed for the SQL Server Database Engine to access any folder where database files are stored.

My organization has dozens of database servers, each of which are storing files in a varying number of local attached drives.

Is there an automated SQL (or PowerShell) script I can run that will return my ALL of the folders that do (or don’t) have Full Control permissions for the NT SERVICE\MSSQLSERVER SID?

I have already tried using Test-DbaPath from DBATools. This is returning results if the folder exists, however it is not letting me know if the SID has Full Control.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

All you need is a loop to go through each of your SQL servers and to loop through your data/log files. Yes its doable, Try the below on one of your servers.
Don’t forget to change the folder name.

$Folder = "D:\TEST"
$User = "MSSQLSERVER"
$permission = (Get-Acl $Folder).Access | ?{$_.IdentityReference -match $User} | Select IdentityReference,FileSystemRights
If ($permission){
$permission | % {Write-Host "User $($_.IdentityReference) has '$($_.FileSystemRights)' rights on folder $folder"}
}
Else {
Write-Host "$User Doesn't have any permission on $Folder"
}

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply