sp_displayroles

Displays all roles granted to another role, login or login profile, the entire hierarchy tree of roles in table format, and other login security-related parameters configured for the specified role, including the date when the role was locked, its reason, and the login server user ID (suid) that locked the role. For password-protected roles, also displays the role password encryption version.

Displays roles granted to logins through an associated login profile. A grantee column in the output displays the login profile name as applicable. This column is only displayed if the login has an associated login profile with roles granted to the login. The login profile association could be direct or through a default login profile.

Syntax

sp_displayroles [grantee_name [, mode]]

Parameters

grantee_name – is the login name of a user or login profile name with roles that you want information about, or the name of a role you want information about.
mode – is one of the following:
- expand_up – shows the role hierarchy tree for the parent levels
- expand_down – shows the role hierarchy tree for the child levels
- display_info – shows the login security-related parameters configured for the specified role

Examples

Example 1 – Displays all roles granted to the user issuing the command:

sp_displayroles

Role Name 
------------------------------
supervisor_role

Example 2 – Displays all roles granted to supervisor_role:

sp_displayroles "supervisor_role"

Role Name 
------------------------------
clerk

Example 3 – Displays the roles granted to login “susanne” and the roles below it in the hierarchy:

sp_displayroles susanne, expand_down

Role Name         Parent Role Name       Level 
----------------- ---------------------- ------
supervisor_role   NULL                     1
clerk_role       supervisor_role          2

Example 4 – Displays the roles granted to intern_role and the roles above it in the hierarchy:
```
sp_displayroles "intern_role", expand_up
```

Example 5 – Shows the login security-related parameters configured for the specified role:

sp_displayroles physician_role, "display_info"

Role name = physician_role
Locked : YES
    Date when locked: Jul 14 2007 9:15AM
    Reason: Role locked by SAP ASE due to failed login
    attempts reaching max failed logins.
    Locking suid: dr_john
Date of Last Password Change : Oct 31 1999 3:33PM
Password expiration interval = 5
Password expired : NO
Minimum password length = 4
Maximum failed logins = 10
Current failed logins = 3
Password encryption version: SHA-256

Example 6 – Displays the roles granted to login “tom,” which is associated with the login profile named “sec_profile”:

grant role sec_role to sec_profile

create login tom with password C0mp13x login profile sec_profile

grant role emp_role to tom
go
sp_displayroles tom
go

Role Name                          Grantee 
-------------------------------------------------
emp_role                           tom
sec_role                           sec_profile

Usage

When you specify the optional parameter expand_up or expand_down all directly granted roles contained by or containing the specified role name are displayed.

The Grantee column displays only when a login has an associated login profile, or the default login profile is applicable to the login with role(s) granted to it.

Permissions

The permission checks for sp_displayroles differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage roles` or `manage server` privilege. Any can execute sp_displayroles to see the roles granted to themselves.
Disabled	With granular permissions disabled, you must be a user with sso_role. Any can execute sp_displayroles to see the roles granted to themselves.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect