script to find Privileges for SQL server service account
Maandag 03 Juni 2013 at 4:14 pm. Gebruikte Tags: powershell, sqlserverFor optimal performance it is often advised to grant two privileges to the SQL Server service account. In order to accelerate database creation and alteration, instant file initialization could be performed, without zeroing out the new file space. To do so the service account needs the privilege 'Perform volume maintenance tasks'. The other privilege is called 'Lock pages in memory'. Both are set in the local policy.
Run the GPEdit.mmc and find Computer Configuration > Windows Settings > Security Settings > Local Policies > User Rights Assignment
Of course I wanted a command line solution but this wasn't easy to find. After trying WMI, Windows Registry, PSCX module, NTRights, RSOP, GPResult I finally found a way to show which accounts have those privilages using secedit. This is the script:
function Translate-SIDToUserName ([string] $SID )
{
$objSID = New-Object System.Security.Principal.SecurityIdentifier ("$SID")
$objUser = $objSID.Translate( [System.Security.Principal.NTAccount])
$objUser.Value
}
function checkPrivileges ([string]$servername)
{
$Privilegefile = "\\$servername\c$\scripts\PrivilegeRights.inf"
Invoke-Command -ComputerName $servername -ScriptBlock { secedit /export /areas USER_RIGHTS /cfg c:\scripts\PrivilegeRights.inf >> $null }
"Perform volume maintenance tasks Privilege (for Instant File Initialization) granted to:`n"
(Get-Content $Privilegefile | Where {$_ -like 'SeManageVolumePrivilege*'}).substring(26).split(",").replace("`*","") |
Foreach{ Translate-SIDToUserName $_ }
"`n`nLock Pages in Memory Privilege granted to:`n"
(Get-Content $Privilegefile | Where {$_ -like 'SeLockMemoryPrivilege*'}).substring(24).split(",").replace("`*","") |
Foreach{ Translate-SIDToUserName $_ }
Remove-Item $Privilegefile
}
checkprivileges SQLServerName
Geen reacties