sp_displayroles system procedure

sp_displayroles(
user_role_name
, display_mode
, grant_type
)

user_role_name Use this CHAR(128) parameter to specify the name of a system privilege, system role, user-defined role, or user name. If it is not specified or is NULL, then the current user is used by default.
display_mode Use this VARCHAR(30) parameter to specify whether to return parent-level or child-level hierarchy, relative to user_role_name. If display_mode is not specified or is NULL, then only explicitly granted roles and privileges are returned (no inherited roles or privileges). Possible values for display_mode include the following:
- expand_up Shows the system roles granted to user_role_name in the parent hierarchy tree for user_role_name.
- expand_down Shows the system roles and privileges granted to user_role_name, including the role hierarchy tree for the child levels of user_role_name.
grant_type Use this VARCHAR(30) parameter to control the grant type returned. If it is not specified, then ALL is used by default. Possible values for grant_type including the following:
- NO_ADMIN Shows all roles and system privileges granted to user_role_name with the WITH NO ADMIN OPTION or WITH ADMIN OPTION clause.
- ADMIN Returns all roles and system privileges granted to user_role_name with the WITH ADMIN OPTION or WITH ADMIN ONLY OPTION clause.
- ALL Shows all roles/system privileges granted to user_role_name.

Column name	Data type	Description
role_name	CHAR(128)	The role or system privilege granted to user_role_name.
parent_role_name	CHAR(128)	The role names for the parents of user_role_name.
grant_type	CHAR(10)	Information about whether user_role_name has administrative rights. Possible values: NO ADMIN, ADMIN, or ADMIN ONLY.
role_level	SMALLINT	With expand_down mode: Level is 1 for directly granted roles, 2 for the next level below, and so on. With expand_up mode: Level is 0 for the roles to which user_role_name has been granted, -1 for the next hierarchy above, and so on.

For system privileges, the result shows the system privilege name instead of the system privilege role name. With expand_down mode, the parent_role_name is NULL for level 1 (directly granted roles). With the default mode, the role_level column is 1 and parent_role_name is NULL, since with default mode only the directly granted roles are displayed.

If this procedure is used for a user with mode expand_up, then no results are returned since a user resides at the top level in any role hierarchy. Similarly, if this procedure is used for an immutable system privilege, with mode expand_down, then no results are returned because an immutable system privilege resides at the bottom level in any role hierarchy. The default mode is to display only the directly granted roles/system privileges.

SQL/2008 Vendor extension.

No privileges are required to execute this procedure on yourself. However, to return the system privileges or roles for another user ID or a role, you must have the MANAGE ROLES system privilege.

None

The following statement 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
PUBLIC	(NULL)	NO ADMIN	1
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_DBA_ROLE	SYS_RUN_REPLICATION_ROLE	ADMIN	-3
SYS_AUTH_SSO_ROLE	SYS_AUTH_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 ONLY	0

The following statement returns all system privileges granted to the user User1:

CALL sp_displayroles( 'User1' );

This example returns the list of system privileges pertaining to views:

SELECT sys_priv_name FROM sp_sys_priv_role_info() 
WHERE sys_priv_name LIKE '%VIEW%'

sp_displayroles system procedure

Syntax

Arguments

Result set

Remarks

Standards and compatibility

Privileges

Side effects

See also

Example