lowlydba.sqlserver.user_role module – Configures a user’s role in a database.

Note

This module is part of the lowlydba.sqlserver collection (version 2.8.1).

It is not included in ansible-core. To check whether it is installed, run ansible-galaxy collection list.

To install it, use: ansible-galaxy collection install lowlydba.sqlserver. You need further requirements to be able to use this module, see Requirements for details.

To use it in a playbook, specify: lowlydba.sqlserver.user_role.

New in lowlydba.sqlserver 2.4.0

Synopsis

  • Adds or removes a user’s role in a database.

  • Use the roles option to work with multiple roles at once using the add/remove/set pattern.

Requirements

The below requirements are needed on the host that executes this module.

Parameters

Parameter

Comments

database

string / required

Database for the user.

role

string

The database role for the user to be modified.

Deprecated: This parameter is deprecated and will be removed in version 3.0.0. Use roles instead.

roles

dictionary

added in lowlydba.sqlserver 2.8.0

A dictionary of roles to manage for the user.

Supports three keys add, remove, and set.

add adds the user to the specified roles. An empty list is a no-op and returns current membership.

remove removes the user from the specified roles. An empty list is a no-op and returns current membership.

set replaces all current roles with the specified roles. An empty list removes all role memberships.

set cannot be combined with add or remove.

At least one key must be present.

add

list / elements=string

A list of role names to add the user to. May be empty to query current membership without changes.

remove

list / elements=string

A list of role names to remove the user from. May be empty to query current membership without changes.

set

list / elements=string

A list of role names that replaces the user’s current roles. An empty list removes all role memberships.

sql_instance

string / required

The SQL Server instance to modify.

sql_password

string

Password for SQL Authentication.

sql_username

string

Username for SQL Authentication.

state

string

Desired state of the user role membership.

Only applicable when using the role parameter (legacy mode). Cannot be used with roles.

Choices:

  • "present"

  • "absent"

username

string / required

Name of the user.

Attributes

Attribute

Support

Description

check_mode

Support: full

Can run in check_mode and return changed status prediction without modifying target.

platform

Platforms: all

Target OS/families that can be operated against.

Examples

- name: Add a user to a fixed db role (legacy)
  lowlydba.sqlserver.user_role:
    sql_instance: sql-01.myco.io
    username: TheIntern
    database: InternProject1
    role: db_owner

- name: Remove a user from a fixed db role (legacy)
  lowlydba.sqlserver.user_role:
    sql_instance: sql-01.myco.io
    username: TheIntern
    database: InternProject1
    role: db_owner
    state: absent

- name: Add user to multiple roles
  lowlydba.sqlserver.user_role:
    sql_instance: sql-01.myco.io
    username: TheIntern
    database: InternProject1
    roles:
      add:
        - db_owner
        - db_datareader

- name: Remove user from multiple roles
  lowlydba.sqlserver.user_role:
    sql_instance: sql-01.myco.io
    username: TheIntern
    database: InternProject1
    roles:
      remove:
        - db_owner
        - db_datareader

- name: Set user's roles (replace all current roles)
  lowlydba.sqlserver.user_role:
    sql_instance: sql-01.myco.io
    username: TheIntern
    database: InternProject1
    roles:
      set:
        - db_datareader
        - db_datawriter

- name: Combine add and remove operations
  lowlydba.sqlserver.user_role:
    sql_instance: sql-01.myco.io
    username: TheIntern
    database: InternProject1
    roles:
      add:
        - db_securityadmin
      remove:
        - db_owner

Return Values

Common return values are documented here, the following are the fields unique to this module:

Key

Description

data

dictionary

For the roles parameter - a summary object containing current role membership and any roles added or removed.

For the legacy role parameter - output from Add-DbaDbRoleMember or Remove-DbaDbRoleMember. Not returned in check_mode.

Returned: success

added

list / elements=string

List of roles that were added (or would be added in check_mode).

Returned: success

removed

list / elements=string

List of roles that were removed (or would be removed in check_mode).

Returned: success

roleMembership

list / elements=string

List of roles the user is currently a member of. In check_mode reflects state before any changes.

Returned: success

Sample: ["db_owner", "db_datareader"]

Authors

  • John McCall (@lowlydba)