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).

You might already have this collection installed if you are using the ansible package. 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.

dbatools PowerShell 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)

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

Synopsis

Requirements

Parameters

Attributes

Examples

Return Values