Display Roles Granted

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.

The report includes role name, parent role name, type of grant (with or without administrative privilege) and the level of the role hierarchy.

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.

Example

The following statement returns all roles granted to the user issuing the command. In this example, the user logged has been granted the SYS_AUTH_DBA_ROLE compatibility role with administrative rights (for example, GRANT ROLE SYS_AUTH_DBA_ROLE TO User1 WITH ADMIN OPTION;).

CALL sp_displayroles();

This examples returns the list of system privileges granted to the SYS_SPATIAL_ADMIN_ROLE system role:

CALL sp_displayroles( 'SYS_SPATIAL_ADMIN_ROLE' );

role_name	parent_role_name	grant_type	role_level
MANAGE ANY SPATIAL OBJECT	(NULL)	NO ADMIN	1

This examples returns the list of system privileges granted to the SYS_SPATIAL_ADMIN_ROLE, including all roles above it in the hierarchy of roles:

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