sp_displayroles System Procedure

Displays all roles granted to a user-defined role or a user, or displays the entire hierarchical tree of roles.

Syntax

sp_displayroles(
   [ user_role_name ], 
   [ display_mode ],
   [ grant_type ] )

Parameters

  • user_role_name – valid values are:
    • A valid system privilege name or system privilege role name
    • A valid user-defined role name
    • A valid user name

    By default, if no argument is specified, the current login user is used.

  • display_mode – valid values are:
    • EXPAND_UP – shows all roles granted the input role or system privilege; that is the role hierarchy tree for the parent levels.
    • EXPAND_DOWN – shows all roles or system privileges granted to the input role or user; that is, the role hierarchy tree for the child levels.

    If no argument is specified (default), only the directly granted roles or system privileges appear.

  • grant_type – valid values are:
    • ALL – shows all roles or system privileges granted.
    • NO_ADMIN – shows all roles or system privileges granted with the WITH NO ADMIN OPTION or WITH ADMIN OPTION clause.
    • ADMIN – shows all roles or system privileges granted with the WITH ADMIN OPTION or WITH ADMIN ONLY OPTION clause.

    If no argument is specified, ALL is used.

Privileges

You must have EXECUTE privilege on the system procedure. To execute this procedure against other users, you must have the MANAGE ROLES system privilege. To execute against a role or system privilege, you must be an administrator of the role or have administrative rights to the system privilege.

Remarks

Column Name Data Type Description
role_name char(128) Lists role/system privilege name.
parent_role_name char(128) Lists role name of the parent.
grant_type char(10) Lists grant type.
role_level smallint For Expand_down mode, 1 indicates directly granted roles; 2 indicates the next hierarchy below, and so on. For Expand_up mode, 0 indicates the roles to which the specified role is granted; -1 indicates the next hierarchy above, and so on.

For Name = System privilege name, the results show the system privilege name instead of the system privilege role name.

For Mode = Expand_down, parent_role_name is NULL for level 1 (directly granted roles). If no mode is specified (default), role_level is 1 and parent_role_name is NULL, since only directly granted roles appear.

For Name = User name, with Mode = expand_up, no results are returned since a user resides at the top level in any role hierarchy. Similarly, if Name = an immutable system privilege name, with Mode = Expand_down, no results are returned because an immutable system privilege resides at the bottom level in any role hierarchy.

For default Mode, parent_role_name column is NULL and role_level is 1.

Example

This example assumes these GRANT statements have been executed:

GRANT SERVER OPERATOR TO r4;
GRANT BACKUP DATABASE TO r3 WITH ADMIN OPTION;
GRANT DROP CONNECTION TO r3 WITH ADMIN ONLY OPTION;
GRANT MONITOR TO r2;GRANT CHECKPOINT TO r1;
GRANT ROLE r2 TO r1 WITH ADMIN OPTION;
GRANT ROLE r3 TO r2 WITH NO ADMIN OPTION;
GRANT ROLE r4 TO r3 WITH ADMIN ONLY OPTION;
GRANT ROLE r1 TO user1;
GRANT ROLE r1 TO r7;
GRANT ROLE r7 TO user2 WITH ADMIN OPTION;
GRANT BACKUP DATABASE TO user2 WITH ADMIN ONLY OPTION;
sp_displayroles( 'user2', 'expand_down', 'ALL' ) produces output similar to:
role_name parent_role_name grant_type role_level
r7 NULL ADMIN 1
PUBLIC NULL NO ADMIN 1
BACKUP DATABASE NULL ADMIN ONLY 1
dbo PUBLIC NO ADMIN 2
r1 r7 NO ADMIN 2
r2 r1 ADMIN 3
CHECKPOINT r1 NO ADMIN 3
r3 r2 NO ADMIN 4
MONITOR r2 NO ADMIN 4
r4 r3 ADMIN ONLY 5
BACKUP DATABASE r3 ADMIN 5
DROP CONNECTION r3 ADMIN ONLY 5

sp_displayroles( 'user2', 'expand_down', 'NO_ADMIN' ) produces output similar to:

role_name parent_role_name grant_type role_level
r7 NULL ADMIN 1
PUBLIC NULL NO ADMIN 1
dbo PUBLIC NO ADMIN 2
r1 r7 NO ADMIN 2
r2 r1 ADMIN 3
CHECKPOINT r1 NO ADMIN 3
r3 r2 NO ADMIN 4
MONITOR r2 NO ADMIN 4
BACKUP DATABASE r3 ADMIN 5

sp_displayroles( 'r3', 'expand_up', 'NO_ADMIN' ) produces out put similar to:

role_name parent_role_name grant_type role_level
r1 r7 NO ADMIN -2
r2 r1 ADMIN -1
r3 r2 NO ADMIN 0

sp_displayroles( 'r1', 'NO_ADMIN', 'expand_up') produces output similar to:

role_name parent_role_name grant_type role_level
r1 r7 NO ADMIN 0