Check File System for SQL Server Access across multiple servers



  • https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-file-system-permissions-for-database-engine-access?view=sql-server-ver15&redirectedfrom=MSDN&viewFallbackFrom=sql-server-2014 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 https://docs.dbatools.io/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.



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



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2