What if you had to create a script stack that installs SQL Server from an image library? And customisations – such as adding the SQL Server Start up account to the Lock Pages in Memory Local Policies?

If this needs to be rolled out quickly across hundreds of servers, or just one, then there is a way to do this using Group Policy Security Templates and Security Database. – along with secedit, PSExec and Powershell

secedit.exe is a command line tool and comes with Windows. You would use Powershell to call secedit.exe, pass a couple of parameters and job done!

Main steps are:

Step 1: Create Security template and Security Database

Step 2: Write syntax for secedit.exe

Step 3: Create Powershell script to grab parameters and execute the command

Before you start – create a MMC consoled and add 2 snap ins

1)Security Configuration and Analysis

2)Security templates

Step 1: Create Security template and Security Database

Expand Security Templates and right click on the security path: Click New template: Create a template name – for this example I’m using “sqlmemlock”. Click OK and you should see the template appear on the template list.

Expand the template “sqlmemlock” | Local Policies | User Rights Assignment. I added LOCAL SERVICE for the example, but normally I have a dedicated SQL Server Start Up acct.

You should now have something like this:

Next – create the Security database. Right click the snap-in Security configuration and Analysis| Open Database and then add a filename: for the example: I’m using “sqlmemlockdb” | Click Open | Import Template: “sqlmemlock” (the one you created a few minutes ago).

Right click on Security configuration and Analysis and you’ll see 2 options: Analyze this computer and Configure Computer now. Analyze this computer is useful to do a pre and post check.

Once you’ve done the analysis – choose Configure now and using gpedit.msc – you should see the desired result.

If this has not worked – troubleshoot and ensure you have this part working!

Step 2: Draft syntax for secedit.exe

Assuming – you’ve got the template in “C:\windows\security\templates\” and and sdb in “C:\windows\security\databases\”

The syntax is:

Secedit /configure /cfg templatefilename /db databasefilename/overwrite /log logfilename

For our example:

Secedit /configure /cfg C:\windows\security\templates\sqlmemlock.inf /db sqlmemlockdb.sdb /log C:\windows\security\logs\sqlmemlock.log

Step 3: Create Powershell script to grab parameters and execute the command

There are many ways to execute the script on multiple servers. Powershell & psexec – are fantastic tools – which can be used to manage your servers.

This is just one way of managing your servers

1 - Write the above syntax into a cmd file – I’ll call it – lockpages.cmd

Secedit /configure /cfg C:\windows\security\templates\sqlmemlock.inf /db sqlmemlockdb.sdb /log C:\windows\security\logs\sqlmemlock.log

2 - Drag sqlmemlock.cmd onto the remote server

3 - Create a powershell script and write this syntax:

cmd /c psexec \\MYSERVER1 "C:\sqlunattended\lockpages.cmd"

3 - Execute the powershell command – and presto!

There are many possibilities of combining this process with a list of servers. Syntax that can iterate a list of servers. Check this post Powershell: SQLCMD and invoke-expression - adapt the syntax to your purposes

If you want syntax to to iterate through a list of servers, let me know and I’ll post.

Republished from http://www.sqlserver-dba.com.