sp_displayroles

Description

Displays all roles granted to another role, or displays the entire hierarchy tree of roles in table format.

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 : NO
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

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.

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