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.

  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

Tags: , , ,

About Kendra Little

Kendra specializes in high availability and performance tuning. She is a Microsoft Certified Master in SQL Server-- the highest technical SQL Server Certification available. Kendra loves databases and software development more than long walks on the beach. Those cartoons in her blog posts? She draws 'em all. You should follow Kendra on Twitter: http://twitter.com/kendra_little

5 Responses to “Automating SQL Local Security Policy Rights: PoSH and NTRights”

  1. Jerome Duquene December 16, 2011 at 7:53 am #

    Hello Kendra,

    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)

    Cheers,
    Jerome

  2. Wayne November 27, 2012 at 9:25 am #

    Great article Kendra.

    In SQL 2005, the group name starts with SQLServer2005MSSQLUser. Just change the one line to work with 2005-2008R2:
    $sqlgroup= net localgroup|findstr SQLServer|findstr MSSQLUser

    In SQL 2012, is there still a group being made? I’m not seeing one for my SQL 2012 instance…

Trackbacks/Pingbacks

  1. SQLCHAD – Life as a SQL Server DBA » Effect Of “Perform Volume Maintenance Tasks” and SQL Server - July 19, 2012

    […] Little has  a blog post on how to automate this in your environment located at http://www.littlekendra.com/2009/11/12/automating-sql-local-security-policy-rights-posh-and-ntrights… Categories: Admin Tags: admin, sql Comments (0) Trackbacks (0) Leave a comment […]

  2. Technical: Microsoft – SQL Server – v2012 – In-Memory Database and Max Server Memory | Daniel Adeniji's – Learning in the Open - May 10, 2014

    […] http://www.littlekendra.com/2009/11/12/automating-sql-local-security-policy-rights-posh-and-ntrights… […]

Leave a Reply