The sp_displayroles stored procedure which returns all roles granted to the specified system privilege, system role, user-defined role, or user name, or displays the entire hierarchy tree of roles.
No system privileges are required to execute the procedure on your own user ID. To execute the procedure on other users requires the MANAGE ROLES system privilege. To execute the procedure for a role or system privilege requires administrative privilege over the role or system privilege specified.
CALL sp_displayroles();
CALL sp_displayroles( 'SYS_SPATIAL_ADMIN_ROLE' );
role_name | parent_role_name | grant_type | role_level |
---|---|---|---|
MANAGE ANY SPATIAL OBJECT | (NULL) | NO ADMIN | 1 |
CALL sp_displayroles( 'SYS_SPATIAL_ADMIN_ROLE', 'expand_up');
role_name | parent_role_name | grant_type | role_level |
---|---|---|---|
SYS_AUTH_DBA_ROLE | dbo | ADMIN | -3 |
SYS_AUTH_SSO_ROLE | SYS_AUTH_DBA_ROLE | ADMIN | -3 |
MANAGE ROLES | SYS_AUTH_REMOTE_DBA_ROLE | ADMIN | -2 |
MANAGE ROLES | SYS_AUTH_SSO_ROLE | ADMIN | -1 |
MANAGE ROLES | SYS_REPLICATION_ADMIN_ROLE | NO ADMIN | -1 |
SYS_SPATIAL_ADMIN_ROLE | MANAGE ROLES | ADMIN | 0 |