For performance reasons, it is generally recommended that the Windows Service Account used by SQL Server be given permissions to Perform Volume Maintenance Tasks (i.e. Instant File Initialization) and to Lock Pages in Memory. (For more on why these permissions should be granted, see Jes Schultz Borland’s Will Instant File Initialization Really Help My Databases? and Jonathan Kehayias’ Great SQL Server Debates: Lock Pages in Memory.)

I work in an environment with dozens of production SQL Servers with seasonal peak workloads occurring twice yearly. One of my team’s tasks prior to each peak season is to confirm that each of our production SQL Servers have these specific permissions enabled. This can be accomplished by logging on manually to each server and reviewing the User Rights Assignments of the Local Security Policy. But who has the time for that?? (Or, this could be a great job for the intern… 🙂 To accomplish this task efficiently via automation, I’ve written a PowerShell script that will iterate through a list of servers and return the requested Security Policy information.

To execute the script, you’ll need to create a “Servers.txt” file containing all the servers to check. The Servers.txt file should be saved in the same directory as the PowerShell script and should look like this (Just replace “Server#” with your real server names.):

Next, save the PowerShell Script below as a .ps1 file. (In the example shown below, I’ve named the file ValidatePermissions.ps1)

$ServerList = Get-Content Servers.txt

foreach ($Server in $ServerList)
{

Invoke-Command -ComputerName $Server -ScriptBlock {

Set-Location C:\

$LocalSvr = get-content env:computername

$proc = Get-CimInstance Win32_Process -Filter “name = 'sqlservr.exe'”
$CimMethod = Invoke-CimMethod -InputObject $proc -MethodName GetOwner

$objUser = New-Object System.Security.Principal.NTAccount($CimMethod.Domain, $CimMethod.User)
$strSID = $objUser.Translate([System.Security.Principal.SecurityIdentifier])
$NTName = $strSID.Value

$ManageVolumePriv = 0
$LockPagesPriv = 0

secedit /export /areas USER_RIGHTS /cfg UserRights.inf /quiet

$FileResults = Get-Content UserRights.inf

Remove-Item UserRights.inf

foreach ($line in $FileResults)
{
if($line -like "SeManageVolumePrivilege*" -and $line -like "*$NTName*")
{
$ManageVolumePriv = 1
}

if($line -like "SeLockMemoryPrivilege*" -and $line -like "*$NTName*")
{
$LockPagesPriv = 1
}
}

Write-Host "Server:" $LocalSvr -foregroundcolor black -backgroundcolor gray
Write-Host " Lock Pages In Memory:" $LockPagesPriv
Write-Host " Instant File Initialization:" $ManageVolumePriv

}

}
 

Once you’ve created the ValidatePermissions.ps1 file and the Servers.txt file, open a PowerShell window and execute the script. In the example below, I’ve saved both files to the folder “C:\PowerShell”.

If any of the SQL Servers checked were lacking permissions, a “0” would return instead of a “1“. Rather than spending 20 minutes checking these servers manually, we’ve now confirmed the appropriate permissions in seconds! This script can be run anytime as needed with no further configuration.

Hopefully, this timesaver will allow you more of an opportunity to work on your 9-iron!