How to Check Instant File Initialization and Lock Pages in Memory on All Your SQL Servers with PowerShell

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.):

Servers

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”.

PowerShellResult

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!

2 thoughts on “How to Check Instant File Initialization and Lock Pages in Memory on All Your SQL Servers with PowerShell

  1. Great script, this works well.

    I modified it a bit. Added some commands after line 10 and modified line 12.
    Used this link as a reference to get the process owner and domain.
    https://blogs.technet.microsoft.com/heyscriptingguy/2015/02/27/get-process-owner-and-other-info-with-wmi-and-powershell/
    But modification below will only work if SQL Server is already installed on the server.

    See modified command below.

    $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

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s