Displays all roles granted to another role, or displays the entire hierarchy tree of roles in table format.
sp_displayroles [grantee_name [, mode]]
is the login name of a user whose roles you want information about, or the name of a role you want information about.
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
Displays all roles granted to the user issuing the command:
sp_displayroles
Role Name ------------------------------ supervisor_role
Displays all roles granted to supervisor_role:
sp_displayroles "supervisor_role"
Role Name ------------------------------ clerk
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
Displays the active roles granted to intern_role and the roles above it in the hierarchy:
sp_displayroles "intern_role", expand_up
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
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.
See “User-Defined Login Security” in the System Administration Guide for more information.
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.
Commands alter role, create role, drop role, grant, revoke, set
System procedures sp_activeroles, sp_displaylogin, sp_helprotect, sp_modifylogin
