I have created two scripts to detect file permissions and LogOn Security Permissions for NT SERVICE\\MSSQLSERVER <\/p>\n\n\n\n
# SQL Server File Permissions Checker - Updated with Auto-Install\n# Run as Administrator\n# Dynamically queries SQL for log file (.ldf) paths and checks permissions on the actual files\n\n# Account to check\n$EngineAccount = \"NT SERVICE\\MSSQLSERVER\"\n\n# SQL Instance name (default; for named, use \"ServerName\\InstanceName\")\n$SqlInstance = \".\"\n\n# Auto-install SqlServer module if missing\nif (-not (Get-Module -ListAvailable -Name SqlServer)) {\n Write-Host \"Installing SqlServer module with -AllowClobber...\" -ForegroundColor Yellow\n Install-Module -Name SqlServer -AllowClobber -Force -Scope CurrentUser\n}\n# Import the module\nImport-Module SqlServer -ErrorAction Stop\n\n# Function to query SQL log file paths (.ldf)\nfunction Get-SqlLogFiles {\n # Query for all log file paths (type = 1 for logs)\n $filesQuery = \"SELECT DISTINCT physical_name FROM sys.master_files WHERE type = 1;\"\n $filesResult = Invoke-Sqlcmd -ServerInstance $SqlInstance -Query $filesQuery -TrustServerCertificate\n $logFiles = $filesResult | ForEach-Object { $_.physical_name } | Where-Object { $_ -like \"*.ldf\" }\n\n return $logFiles\n}\n\n# Get .ldf files dynamically\n$LogFiles = Get-SqlLogFiles\n\nif ($LogFiles.Count -eq 0) {\n Write-Host \"No .ldf files retrieved from SQL. Check SQL connection\/module.\" -ForegroundColor Red\n return\n}\n\n# Check permissions for the engine account on each .ldf file\n$Accounts = @($EngineAccount)\nforeach ($file in $LogFiles) {\n if (Test-Path $file) {\n $acl = Get-Acl $file\n\n Write-Host \"File: $file\" -ForegroundColor Green\n foreach ($acct in $Accounts) {\n $access = $acl.Access | Where-Object { $_.IdentityReference -eq $acct }\n\n Write-Host \" Account: $acct\"\n if ($access) {\n Write-Host \" Permissions: $($access.FileSystemRights)\" -ForegroundColor Green\n if ($access.FileSystemRights -match \"FullControl|Modify\") {\n Write-Host \" Status: Good (has write access)\" -ForegroundColor Green\n } else {\n Write-Host \" Status: Insufficient (needs Modify or Full Control)\" -ForegroundColor Red\n }\n } else {\n Write-Host \" NO access - Grant if needed\" -ForegroundColor Yellow\n }\n Write-Host \"\"\n }\n Write-Host \"-----\"\n } else {\n Write-Host \"File not found: $file\" -ForegroundColor Yellow\n }\n}\n\nWrite-Host \"Check complete! Checked permissions on actual .ldf log files.\" -ForegroundColor Cyan<\/code><\/pre>\n\n\n\n# Ultimate SQL Virtual Account Permissions Checker - Correct SIDs\n# Run as Administrator\n# Uses correct standard SIDs for default instance services\n\nWrite-Host \"SQL Server Virtual Account Permissions Checker (Correct SIDs)\" -ForegroundColor Cyan\nWrite-Host \"==============================================================\" -ForegroundColor Cyan\nWrite-Host \"\"\n\n# SQL services to check (default instance)\n$SqlServices = @(\n @{\n ServiceName = \"MSSQLSERVER\"\n Display = \"SQL Server Engine (MSSQLSERVER)\"\n VirtualAccount = \"NT SERVICE\\MSSQLSERVER\"\n SID = \"S-1-5-80-3880718306-3832830129-1677859214-2598158968-1052248003\"\n },\n @{\n ServiceName = \"SQLSERVERAGENT\"\n Display = \"SQL Server Agent (SQLSERVERAGENT)\"\n VirtualAccount = \"NT SERVICE\\SQLSERVERAGENT\"\n SID = \"S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430\"\n }\n)\n\n# Check current logon accounts\nforeach ($svc in $SqlServices) {\n $service = Get-Service -Name $svc.ServiceName -ErrorAction SilentlyContinue\n if (-not $service) {\n Write-Host \"$($svc.Display): Service not installed\" -ForegroundColor Yellow\n continue\n }\n\n $wmi = Get-WmiObject Win32_Service -Filter \"Name='$($svc.ServiceName)'\"\n $account = $wmi.StartName\n\n Write-Host \"$($svc.Display)\" -ForegroundColor Green\n Write-Host \" Status: $($service.Status)\"\n if ($account -eq $svc.VirtualAccount) {\n Write-Host \" Logon : $account (Recommended virtual account)\" -ForegroundColor Green\n } else {\n Write-Host \" Logon : $account\" -ForegroundColor Yellow\n }\n Write-Host \"\"\n}\n\n# Export local security policy\n$tempFile = [IO.Path]::GetTempFileName()\nsecedit \/export \/cfg $tempFile \/quiet | Out-Null\n$content = Get-Content $tempFile\nRemove-Item $tempFile -Force\n\n# Define privileges\n$Privileges = @(\n @{ Name = \"SeServiceLogonRight\"; Display = \"Log on as a service\"; GroupGrant = $true },\n @{ Name = \"SeAssignPrimaryTokenPrivilege\"; Display = \"Replace a process-level token\"; GroupGrant = $false },\n @{ Name = \"SeIncreaseQuotaPrivilege\"; Display = \"Adjust memory quotas for a process\"; GroupGrant = $false },\n @{ Name = \"SeChangeNotifyPrivilege\"; Display = \"Bypass traverse checking\"; GroupGrant = $false }\n)\n\n# Check each privilege\nforeach ($priv in $Privileges) {\n Write-Host \"Checking $($priv.Display) ($($priv.Name))...\" -ForegroundColor Cyan\n\n $line = $content | Where-Object { $_ -match \"^$($priv.Name)\\s*=\" }\n if (-not $line) {\n Write-Host \" Privilege not found in policy export.\" -ForegroundColor Yellow\n Write-Host \"\"\n continue\n }\n\n $assignments = ($line -split \"=\", 2)[1].Trim()\n $entries = $assignments -split \",\" | ForEach-Object { ($_.Trim() -replace '^\\*', '').Trim().ToLower() } | Where-Object { $_ }\n\n $hasAllServicesGroup = $entries -contains \"s-1-5-80-0\"\n $coveredServices = @()\n\n foreach ($svc in $SqlServices) {\n $lowerSid = $svc.SID.ToLower()\n $lowerAccount = $svc.VirtualAccount.ToLower()\n $lowerShort = $svc.ServiceName.ToLower()\n $lowerDoubleSlash = \"nt service\\\\$($svc.ServiceName.ToLower())\"\n\n if ($entries -contains $lowerSid -or $entries -contains $lowerAccount -or $entries -contains $lowerShort -or $entries -contains $lowerDoubleSlash) {\n $coveredServices += $svc.Display\n }\n }\n\n if ($priv.GroupGrant) {\n if ($hasAllServicesGroup) {\n Write-Host \" Granted via NT SERVICE\\ALL SERVICES (S-1-5-80-0) \u2013 covers Engine AND Agent\" -ForegroundColor Green\n } else {\n Write-Host \" NT SERVICE\\ALL SERVICES group not present\" -ForegroundColor Yellow\n }\n }\n\n if ($coveredServices.Count -eq $SqlServices.Count) {\n Write-Host \" Granted to: SQL Server Engine AND SQL Server Agent\" -ForegroundColor Green\n } elseif ($coveredServices.Count -eq 1) {\n Write-Host \" Granted to: $($coveredServices[0]) only\" -ForegroundColor Yellow\n } elseif ($coveredServices.Count -eq 0) {\n Write-Host \" No grant found for Engine or Agent\" -ForegroundColor Red\n } else {\n Write-Host \" Granted to: $($coveredServices -join ', ')\" -ForegroundColor Green\n }\n\n Write-Host \"\"\n}\n\n# Instant File Initialization\nWrite-Host \"Instant File Initialization (SeManageVolumePrivilege):\" -ForegroundColor Cyan\n$ifiLine = $content | Where-Object { $_ -match \"^SeManageVolumePrivilege\\s*=\" }\nif ($ifiLine) {\n $ifiAssign = ($ifiLine -split \"=\", 2)[1].Trim().ToLower()\n if ($ifiAssign -match \"mssqlserver|sqlserveragent|s-1-5-80-|all services|localsystem\") {\n Write-Host \" Granted (good for performance)\" -ForegroundColor Green\n } else {\n Write-Host \" Not granted to SQL services\" -ForegroundColor Yellow\n }\n} else {\n Write-Host \" Not configured\" -ForegroundColor Yellow\n}\n\nWrite-Host \"\"\nWrite-Host \"Check complete! Using standard SIDs for accuracy.\" -ForegroundColor Cyan<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"I have created two scripts to detect file permissions and LogOn Security Permissions for NT SERVICE\\MSSQLSERVER<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-9436","post","type-post","status-publish","format-standard","hentry","category-research"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/posts\/9436","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/comments?post=9436"}],"version-history":[{"count":1,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/posts\/9436\/revisions"}],"predecessor-version":[{"id":9437,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/posts\/9436\/revisions\/9437"}],"wp:attachment":[{"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/media?parent=9436"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/categories?post=9436"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/tags?post=9436"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}