Displays all roles granted to a user-defined role or a user, or displays the entire hierarchical tree of roles.
dbo.sp_displayroles( [user_role_name], [display_mode], [grant_type] )
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 |