sp_displayroles System Procedure

Syntax

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

Arguments

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.

Result Set

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.

Remarks

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.

Privileges

For users and extended users:

No system privilege is required to execute this procedures against themselves.
MANAGE ROLES system privilege is required to execute this procedure for other users.

For roles and system privileges:

Administrative privilege over the role or system privilege is required to execute this procedure.
Role administrators can execute this procedure for roles they administer.

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