Check File System for SQL Server Access across multiple servers

The question:

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 SERVICEMSSQLSERVER 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.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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"
}


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 Comment