Display Roles Granted

The sp_displayroles stored procedure returns all roles that are 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 sp_displayroles 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 example returns all roles granted to the user issuing the command.

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