You've probably seen it happen.  During peak load on a SQL Server instance, the operating system occasionally pages out parts of SQL Server's memory to disk, causing performance slowdowns.  And you've probably read how Lock Pages in Memory can help address this issue.  

Granting this user right can be time-consuming and error-prone when done manually especially if you need to grant the permission on multiple instances.

This is where Ansible comes in.  It allows you to automate changes and maintain configurations across development, staging, and production environments.  

Today, I want to introduce you to how to set this permission using Ansible and cover Ansible Variables.

Ansible variables make it easy to parameterize this configuration, while variable precedence ensures the right value always wins if defined in multiple places.

What are Ansible Variables?

Ansible variables allow you to store and reuse values—like the name of the SQL Server service account or the necessary Windows permission—so you can write flexible, maintainable playbooks. For example, you can manage LPIM (the SeLockMemoryPrivilege right) across different environments by defining variables in the appropriate location, like your role defaults, inventory files, or host_vars.

Below is a minimal playbook snippet that configures LPIM for a SQL Server service account:

- name: Grant Lock Pages in Memory to SQL Service Account

  hosts: sqlservers

  gather_facts: false

  vars:

    service_account_name: "SANDBOX\\SQLService"

    lpim_permission: "SeLockMemoryPrivilege"

  tasks:

    - name: Assign LPIM to the SQL Server service account

      ansible.windows.win_user_right:

        user: "{{ service_account_name }}"

        right: "{{ lpim_permission }}"

        action: add

Here, service_account_name and lpim_permission are standard Ansible variables defined at the play level.

How Variable Precedence Works

You can define the same variable (e.g., service_account_name) in multiple places. Ansible resolves which value to use based on a precedence hierarchy. From lowest to highest:

1. Role defaults (defaults/main.yml in a role)

2. Inventory group vars (group_vars)

3. Inventory host vars (host_vars)

4. Playbook/group/host vars or vars_files

5. Block vars, task vars, and handler vars

6. Role vars (vars/main.yml in a role)

7. Extra vars (-e on the command line)

If a variable is defined in multiple places, the definition with the highest precedence wins. For example, if service_account_name is set to "SANDBOX\\SQLService" in role defaults but you specify it as "SANDBOX\\ProdSQLService" via extra vars, then "SANDBOX\\ProdSQLService" will ultimately be used.

Example: Using LPIM with Different Accounts

Imagine you have two servers—one for development and one for production. You might keep a default service account in a role default file, then override it for production in your inventory’s host_vars or through extra vars:

• Role default (roles/mssql/defaults/main.yml):

service_account_name: "SANDBOX\\SQLServiceDev"

lpim_permission: "SeLockMemoryPrivilege"

• Inventory host_vars (host_vars/sql-prod.yml):

service_account_name: "SANDBOX\\SQLServiceProd"

When running the playbook against sql-prod host, Ansible automatically overrides the default account (SANDBOX\SQLServiceDev) with SANDBOX\SQLServiceProd from the host_vars file.

If you further decide to specify it at runtime:

ansible-playbook -i inventory site.yml -e "service_account_name=SANDBOX\\SQLServiceSpecial"

that extra var will override both the role default and the host_vars value. This level of control and flexibility is exactly what makes Ansible’s variable system so powerful.

Wrapping Up

Understanding variable precedence is crucial for any robust Ansible setup, especially when managing Windows-specific tasks like granting LPIM permissions to your SQL Server service account.

Whether you define your variables in role defaults, host_vars, or pass them via extra vars, the highest priority always wins—helping you keep configurations consistent and your SQL Servers properly configured.

- Luke