120.0_ensure_mssql_aag_local_user.yaml

# Example Playbook for local AAG User
- name: Ensure Local User for SQL
  hosts: windows
  vars_files: 
    - ./vars/main.yml
    - ./vars/agents_vars.yaml
  vars:
    app_credentials_username: 'sql_service_local'
    app_credentials_name: 'bob demouser sql'
    app_credentials_password: 'Password123!'
    mssql_port: 1433
    mssql_user: "Demo\\Administrator"        
  tasks:
  - name: "Ensure user {{ app_credentials_username }} is present"
    ansible.windows.win_user:
      name: "{{ app_credentials_username }}"
      password: "{{ app_credentials_password }}"
      state: present
      groups:
        - Users
        - Administrators

  - name: Check DB connection
    delegate_to: localhost
    community.general.mssql_script:
      login_user: "{{ mssql_user }}"
      login_password: "{{ansible_password }}"
      login_host: "{{ ansible_hostname }}"
      login_port: "{{ mssql_port }}"
      db: master
      script: "SELECT 1"
  - name: Ensure User Login
    delegate_to: localhost
    community.general.mssql_script:
      login_user: "{{ mssql_user }}"
      login_password: "{{ansible_password }}"
      login_host: "{{ ansible_hostname }}"
      login_port: "{{ mssql_port }}"
      db: master
      script: |
        USE [master]
        GO
        IF NOT EXISTS 
          (SELECT name  
            FROM master.sys.server_principals
                WHERE name = '{{ ansible_hostname }}\sql_service_local')
        BEGIN
            CREATE LOGIN [{{ ansible_hostname }}\sql_service_local] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
        END
        GO
        ALTER SERVER ROLE [sysadmin] ADD MEMBER [{{ ansible_hostname }}\sql_service_local]
        GO