Commands / Invoke-sqmNtfsSetup
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.
-EnableExceptionswitch$falsePropagate exceptions immediately instead of logging them as warnings and returning a status object.

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

Example 1, Auto-discover everything and grant FullControl

Invoke-sqmNtfsSetup -SqlInstance "SQL01"

Example 2, Dry run: shows accounts and directories without changing ACLs

Invoke-sqmNtfsSetup -SqlInstance "SQL01\INST01" -Permission Modify -WhatIf

Example 3, Override directories and account explicitly

Invoke-sqmNtfsSetup `
    -Directory 'E:\MSSQL\DATA', 'F:\MSSQL\LOG' `
    -Account 'NT SERVICE\MSSQLSERVER'

Example 4, Check result

$r = Invoke-sqmNtfsSetup -SqlInstance "SQL01"
Write-Host "$($r.Status): $($r.Granted.Count) ACL(s) set | Backup: $($r.BackupFile)"
⬆ Index
All Commands