Skip to content

Instantly share code, notes, and snippets.

@ajomathew
Last active March 7, 2019 08:18
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save ajomathew/4d1f64193d068854227080d5234fd336 to your computer and use it in GitHub Desktop.
Grant SQL Server account access to Lock Pages in Memory using PowerShell
## Powershell script referenced from http://keepingitgeek.blogspot.com/2015/01/grant-sql-server-account-access-to-lock.html
## Powershell script was converted to a DSC module for implementation.
Configuration createServiceUserUser {
param(
# Parameter help description
[Parameter(Mandatory)]
[string]
$VMHostName,
# Parameter help description
[Parameter(Mandatory)]
[pscredential]
$user
)
Import-DscResource -ModuleName 'SqlServerDSC' -ModuleVersion '12.2.0.0'
## Set SeLockMemoryPrivilege
## Variables
$TempLocation = "C:\Temp"
$SQLServiceAccount = $user.UserName #Account used for the SQL Service
$SQLInstance = "MSSQLSERVER"
$ChangeFrom = "SeManageVolumePrivilege = "
$ChangeFrom2 = "SeLockMemoryPrivilege = "
$ChangeTo = "SeManageVolumePrivilege = SQLServerSQLAgentUser$" + $vmHostName + "`$" + "$SQLInstance,"
$ChangeTo2 = "SeLockMemoryPrivilege = $SQLServiceAccount,"
Node $VMHostName {
User CheckServiceAccountPresent {
UserName = $user.UserName
Ensure = 'Present'
PasswordNeverExpires = $true
Password = $user
}
Script SeLockMemoryPrivilege{
GetScript = {
return @{
test = 'results'
}
}
TestScript = {
$TempLocation = "C:\Temp"
$SQLServiceAccount = $user.UserName #Account used for the SQL Service
$SQLInstance = "MSSQLSERVER"
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
IF ((Get-Content $fileName) | where { $_.Contains("SeLockMemoryPrivilege") }){
return $true
}else{
return $false
}
}
SetScript = {
$TempLocation = "C:\Temp"
$SQLServiceAccount = $user.UserName #Account used for the SQL Service
$SQLInstance = "MSSQLSERVER"
$ChangeFrom = "SeManageVolumePrivilege = "
$ChangeFrom2 = "SeLockMemoryPrivilege = "
$ChangeTo = "SeManageVolumePrivilege = SQLServerSQLAgentUser$" + $vmHostName + "`$" + "$SQLInstance,"
$ChangeTo2 = "SeLockMemoryPrivilege = $SQLServiceAccount,"
$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"
}
# DependsOn = '[User]CheckServiceAccountPresent'
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment