sp_displayroles

Description

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

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 Adaptive Server 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

Only a system administrator can execute sp_displayroles to display information on roles granted to any other user. All users can execute sp_displayroles to see the roles granted to them.

Auditing

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

See also