sp_displayroles

Displays all roles granted to another role, login or login profile, the entire hierarchy tree of roles in table format, and other login security-related parameters configured for the specified role, including the date when the role was locked, its reason, and the login server user ID (suid) that locked the role. For password-protected roles, also displays the role password encryption version.

Displays roles granted to logins through an associated login profile. A grantee column in the output displays the login profile name as applicable. This column is only displayed if the login has an associated login profile with roles granted to the login. The login profile association could be direct or through a default login profile.

Syntax

sp_displayroles [grantee_name [, mode]]

Parameters

Examples

Usage

When you specify the optional parameter expand_up or expand_down all directly granted roles contained by or containing the specified role name are displayed.

The Grantee column displays only when a login has an associated login profile, or the default login profile is applicable to the login with role(s) granted to it.

See also:
  • alter role, create role, drop role, grant, revoke, set in Reference Manual: Commands

  • User-Defined Login Security in the System Administration Guide for more information.

Permissions

The permission checks for sp_displayroles differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be a user with manage roles or manage server privilege.

Any can execute sp_displayroles to see the roles granted to themselves.

Disabled

With granular permissions disabled, you must be a user with sso_role.

Any can execute sp_displayroles to see the roles granted to themselves.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference
sp_activeroles
sp_displaylogin
sp_helprotect