LittleKendra.com

The Basics on Local Security Policy Rights and SQL Server…

There are a couple of local security policy rights that are not granted by default in SQL Server setup that I’ve been setting manually for a few years now:

• Lock Pages In Memory
  • Allows large page allocation
  • Prevents the SQL Server process from being paged out
• Perform Volume Maintenance Tasks
  • Instant initialization on data files

It’s a fairly click-heavy process to add the permissions for these through the Local Security Policy GUI. I prefer that these permissions be granted to the local security group for sql server that’s created in Windows, and that really requires a lot of clicks, unless you can remember and enter a group name like this without any typos:

SQLServerMSSQLUser$servername$MSSQLSERVER  or SQLServerMSSQLUser$servername$INSTANCENAME

By the way, what is up with using the $ in the group names?  If someone can tell me, I would love to know.

Why Not Just Grant these Rights to the Domain Service Account?

If your SQL Server service is running under a domain account, you can grant these privileges to that account and it will work. However, I prefer to grant it to the local group for SQL Services for two main reasons.

1. If you change the account for SQL Server services, everything will still work. You can go to another domain account, network service, whatever, you won’t have to remember to check these permissions. (And you won’t remember until you’ve been bitten by it about 5 times.)
2. It’s consistent with the way SQL Server grants permissions itself. (See the rights granted to the local groups here).

What about Clusters?

Well, so much for consistency– clusters do not use the local groups! For clustered instances, we grant the permission to the domain account.

Still, I prefer to be consistent with the way that the product grants the perms on standalone instances, and I have found instances where the service account was changed and the admins either were not aware they needed to grant these permissions or forgot.

Anyway, it Should be Easy!

I always figured there was an easy way to do this. And lo and behold, there is.

I have a project to build out some new pre-production servers right now, so as I’m walking through the build steps I am looking for ways to further automate our configuration. For these two rights, I did some searching and found that using NtRights.exe seems to be the easiest way to grant these permissions. Whenever people ask about editing local security policy with powershell, the response seems to be, “Why aren’t you using a GPO?”  But that wouldn’t work for this case.

NtRights.exe, which is a W2K3 resource kit utility (available here) was created for just these tasks, and I can call it from a PowerShell script anyhoo.

So here is the super-quick, super-dirty 1.0 version of granting these privileges. This version assumes you only have one sql instance installed on the box (which is how we roll in my current environment– otherwise you could pull them into an array and loop).  You can also use NTRights against remote machines, by the way, but you’d have to get the name of the Sql Server group a bit differently in that case. Or just use your domain account, I’m not going to judge you.

In my case this is designed to be run post-setup when we’re logged into the box anyway, and the operator reviews the output. NTRights prints what it is granting and the outcome.

The Script

#copy ntrights.exe
Robocopy \\deployserver\shareWhereNtRightsLivesl\ d:\installDir ntrights.exe
D:
cd D:\installDir

#get the name of the sql server local group
$sqlgroup= net localgroup|findstr SQLServerMSSQLUser

#if we haven't found a group with this name, default to our service account (should be a cluster)
if (!$sqlgroup) {$sqlgroup="DOMAIN\SERVICEACCOUNT"}

$sqlgroup=$sqlgroup.Replace("*","")

.\Ntrights -u $sqlgroup +r SeLockMemoryPrivilege
.\Ntrights -u $sqlgroup +r SeManageVolumePrivilege