Wednesday 28 January 2015

Grant SQL Server account access to Lock Pages in Memory using PowerShell

I was recently tasked by DevonDBA to automate granting a SQL server account the ability to Lock Pages in Memory using a PowerShell Script, this is what I came up with,

# Search for then add SQL group to SecPol and also grant an account to Lock Pages in Memory

# Variables used - change as required
$TempLocation = "C:\Temp"
$SQLServiceAccount = "ServerName\SQLAccount" #Account used for the SQL Service
$SQLInstance = "MSSQLSERVER"

# Variables that you don't need to change

# This is the line we need to change in the cfg file
$ChangeFrom = "SeManageVolumePrivilege = "
$ChangeFrom2 = "SeLockMemoryPrivilege = "

# Build the new line using local computername (needs the ` to escape the $)
$ChangeTo = "SeManageVolumePrivilege = SQLServerSQLAgentUser$" + $env:computername + "`$" + "$SQLInstance,"
$ChangeTo2 = "SeLockMemoryPrivilege = $SQLServiceAccount,"

# Check if temp location exists and create if it doesn't

IF ((Test-Path $TempLocation) -eq $false)
{
New-Item -ItemType Directory -Force -Path $TempLocation
Write-Host "Folder $TempLocation created"
}

# Set a name for the Security Policy cfg file.
$fileName = "$TempLocation\SecPolExport.cfg"

#export currect Security Policy config
Write-Host "Exporting Security Policy to file"
secedit /export /cfg $filename

# Use Get-Content to change the text in the cfg file and then save it
(Get-Content $fileName) -replace $ChangeFrom, $ChangeTo | Set-Content $fileName

# As the line for SeLockMemoryPrivilege only exists if there is something already in the group
# this will check for it and add your $SQLServiceAccount or use Add-Contect to append SeLockMemoryPrivilege and your $SQLServiceAccount
IF ((Get-Content $fileName) | where { $_.Contains("SeLockMemoryPrivilege") })
{
Write-Host "Appending line containing SeLockMemoryPrivilege with $SQLServiceAccount"
(Get-Content $fileName) -replace $ChangeFrom2, $ChangeTo2 | Set-Content $fileName
}
else
{
Write-Host "Adding new line containing SeLockMemoryPrivilege"
Add-Content $filename "`nSeLockMemoryPrivilege = $SQLServiceAccount"
}

# Import new Security Policy cfg (using '1> $null' to keep the output quiet)
Write-Host "Importing Security Policy..."
secedit /configure /db secedit.sdb /cfg $fileName 1> $null
Write-Host "Security Policy has been imported"

2 comments:

  1. Excellent, I have been searching for a way of doing this for quite sometime. And the real bonus is that despite having to alter the script slightly to fit my environment better, it worked first time.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete