sp_activeroles

Description

Displays all active roles.

Syntax

sp_activeroles [expand_down]

Parameters

expand_down

shows the hierarchy tree of all active roles contained by your roles.

Examples

Example 1

Displays all active roles.

sp_activeroles
Role Name
------------------------------
sa_role
sso_role
oper_role
replication_role 

Example 2

Displays active roles and their hierarchy tree:

sp_activeroles expand_down
Role Name       Parent Role Name              Level
-------------------------------- ------------------
sa_role         NULL                              1
doctor_role     NULL                              1
oper_role       NULL                              1

Usage

sp_activeroles displays all your active roles and all roles contained by those roles.

Permissions

Any user can execute sp_activeroles. Permission checks do not differ based on the granular permissions settings.

Auditing

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

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • 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

See also

Documents For information about creating, managing, and using roles, see the System Administration Guide.

Commands alter role, create role, drop role, grant, revoke, set

Functions mut_excl_roles, proc_role, role_contain, role_name

System procedures sp_displayroles