sqmSQLTool › Commands › Invoke-sqmNtfsSetup

Invoke-sqmNtfsSetup Filesystem

Grants the SQL Server service accounts (Engine and Agent) the required NTFS permissions on the instance's data, log, TempDB and backup directories. Auto-discovers accounts via Get-DbaService and directories via Get-DbaDefaultPath + sys.master_files. Saves an ACL backup (SDDL JSON) before making any changes. Supports FullControl or Modify with full -WhatIf/-Confirm support.

Execution Flow

START dbatools available? throw; return Step 1 — Discover service accounts If -Account supplied → use directly | else: Get-DbaService -Type Engine,Agent | filter by instance name No accounts found → Status=NoAccounts; return Step 2 — Discover directories If -Directory supplied → use directly | else: Get-DbaDefaultPath (Data/Log/Backup) + sys.master_files (Data/Log/TempDB) Normalize + deduplicate + filter to existing directories | none found → Status=NoDirectories; return Step 3 — ACL backup (unless -SkipBackup) foreach $dir: Get-Acl → .Sddl → [ {Path, Sddl}, … ] | ConvertTo-Json → NtfsAclBackup_{instance}_{ts}.json Backup failure → Status=BackupFailed; return (no permissions changed) Step 4 — Grant permissions foreach $dir in $dirs | foreach $acct in $accounts ShouldProcess "$acct → $dir (Grant $Permission)" | -WhatIf → log only New FileSystemAccessRule($acct, $rights, ContainerInherit|ObjectInherit, None, Allow) Get-Acl → AddAccessRule → Set-Acl | failure → $failed[] appended Status: Granted | PartialFailure | Failed | WhatIfSkipped Return PSCustomObject { SqlInstance, Accounts[], Directories[], Permission, BackupFile, Granted[], Status, Message } DONE

Synopsis

When -Account and -Directory are both omitted, the function auto-discovers both from the target SQL instance. Service accounts are resolved via Get-DbaService -Type Engine,Agent filtered by the instance name; if no instance-specific match is found, all SQL service accounts on the server are used. Directories come from Get-DbaDefaultPath (Data, Log, Backup) and from sys.master_files (which includes TempDB). Only directories that actually exist (Test-Path -PathType Container) are processed. The ACL backup file is a JSON array of {Path, Sddl} objects that can be used for manual rollback via Set-Acl.

Requires dbatools. Intended to run on the SQL Server host itself. Permissions are applied locally via Set-Acl — no remoting needed.

Syntax

Invoke-sqmNtfsSetup
    [-SqlInstance <String>]             # default: $env:COMPUTERNAME
    [-SqlCredential <PSCredential>]
    [-Account <String[]>]               # override: specific accounts to grant
    [-Directory <String[]>]             # override: specific directories to set
    [-Permission <FullControl|Modify>]  # default: FullControl
    [-BackupPath <String>]              # default: Get-sqmConfig OutputPath
    [-SkipBackup]                        # skip ACL backup (not recommended)
    [-EnableException]
    [-WhatIf] [-Confirm]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString$env:COMPUTERNAMETarget SQL Server instance.
-SqlCredentialPSCredential—SQL auth credentials for the dbatools connection.
-AccountString[]Auto-discoveredExplicit account(s) to grant. Overrides auto-discovery.
-DirectoryString[]Auto-discoveredExplicit directory path(s). Overrides auto-discovery from SQL.
-PermissionStringFullControlFullControl or Modify.
-BackupPathStringGet-sqmConfig OutputPathDirectory for the ACL backup JSON file.
-SkipBackupSwitch$falseSkip the ACL backup. Not recommended — backup failure aborts by default.

Return Value

PropertyDescription
SqlInstanceTarget instance name.
AccountsAccounts that were granted permissions.
DirectoriesDirectories that were processed.
PermissionPermission level applied (FullControl or Modify).
BackupFilePath to the ACL backup JSON file ($null if skipped).
GrantedArray of strings: "account → directory" for each successful assignment.
StatusGranted — all succeeded; PartialFailure — some failed; Failed — all failed; BackupFailed — aborted before granting; NoAccounts/NoDirectories — nothing to process; WhatIfSkipped — dry run.
MessageHuman-readable summary including failure details.

Examples

# Auto-discover everything and grant FullControl
Invoke-sqmNtfsSetup -SqlInstance "SQL01"

# Dry run: shows accounts and directories without changing ACLs
Invoke-sqmNtfsSetup -SqlInstance "SQL01\INST01" -Permission Modify -WhatIf

# Override directories and account explicitly
Invoke-sqmNtfsSetup `
    -Directory 'E:\MSSQL\DATA', 'F:\MSSQL\LOG' `
    -Account 'NT SERVICE\MSSQLSERVER'

# Check result
$r = Invoke-sqmNtfsSetup -SqlInstance "SQL01"
Write-Host "$($r.Status): $($r.Granted.Count) ACL(s) set | Backup: $($r.BackupFile)"