Chapter 1: System Procedures

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:

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

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	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