Automating SQL Local Security Policy Rights: PoSH and NTRights

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.

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

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=&amp;amp;quot;DOMAIN\SERVICEACCOUNT&amp;amp;quot;} $sqlgroup=$sqlgroup.Replace(&amp;amp;quot;*&amp;amp;quot;,&amp;amp;quot;&amp;amp;quot;) .\Ntrights -u $sqlgroup +r SeLockMemoryPrivilege .\Ntrights -u $sqlgroup +r SeManageVolumePrivilege

#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=&amp;amp;quot;DOMAIN\SERVICEACCOUNT&amp;amp;quot;}
$sqlgroup=$sqlgroup.Replace(&amp;amp;quot;*&amp;amp;quot;,&amp;amp;quot;&amp;amp;quot;)
.\Ntrights -u $sqlgroup +r SeLockMemoryPrivilege
.\Ntrights -u $sqlgroup +r SeManageVolumePrivilege

Great article !
I’m wondering the same – What’s the single command line to add a specific group/account into these local security policies (lock page in memory & perform volume maintenance tasks) ?

Until now I found none 🙁 The one you describe is the easier one I found.

As SQL Server 2012 will be compatible with Windows server core and the resource kit utility is no more availaible on W2k8R2 servers, how will we perform this simple, important standard tasks on core servers ?

I would be glad to have a powershell commands to run just as:
set-localSecurityPolicy SeLockMemoryPrivilege -add -SQLserverLocalSecurityGroup (or SID)
set-localSecurityPolicy SeManageVolumePrivilege -add -SQLserverLocalSecurityGroup (or SID)

I did a quick search today and I’m not finding that it’s possible yet— looks like exporting / importing a security policy is the recommended track for Server Core, but it hasn’t been very straightforward for people using it:

I wrote this post back in 2009. My memory was that the team who managed group policy at the time was very busy and was also designing a lot of future changes. On the DBA team we figured we were better off just scripting it, since we’d be responsible if the setting got accidentally reverted at some point caused an issue.

I’m not against using Group Policy at all, I’ve just found that it can be tricky to manage the communication and human aspects of doing it that way since the DBA team usually doesn’t own policies or spend a lot of time reviewing them.