Displays all roles granted to a user-defined role or a user, or displays the entire hierarchical tree of roles.
sp_displayroles(
[ user_role_name ],
[ display_mode ],
[ grant_type ] )
By default, if no argument is specified, the current login user is used.
If no argument is specified (default), only the directly granted roles or system privileges appear.
If no argument is specified, ALL is used.
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.
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.
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;
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 |