0

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.

2
  • 1
    Do you have a list of all your servers and the folders on each server that you want to check?
    – SAKA UK
    Mar 28, 2022 at 15:48
  • @SAKAUK, yes I could definitely put that together Mar 28, 2022 at 15:57

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.