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

grantee_name

is the login name of a user whose roles you want information about, or the name of a role you want information about.

mode

is one of the following:

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 active 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 active 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

Permissions

Only a System Administrator or a System Security Officer can execute sp_displayroles to display information on roles activated by any other user. Any user can execute sp_displayroles to see his or her own active roles.

See also

Commands alter role, create role, drop role, grant, revoke, set

System procedures sp_activeroles, sp_displaylogin, sp_helprotect, sp_modifylogin