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.
sp_displayroles [grantee_name [, mode]]
is the login name of a user or login profile name whose roles you want information about, or the name of a role you want information about.
is one of the following:
expand_up – shows the role hierarchy tree for the parent levels
expand_down – shows the role hierarchy tree for the child levels
display_info – shows the login security-related parameters configured for the specified role
Displays all roles granted to the user issuing the command:
sp_displayroles
Role Name ------------------------------ supervisor_role
Displays all roles granted to supervisor_role:
sp_displayroles "supervisor_role"
Role Name ------------------------------ clerk
Displays the roles granted to login “susanne” and the roles below it in the hierarchy:
sp_displayroles susanne, expand_down
Role Name Parent Role Name Level ----------------- ---------------------- ------ supervisor_role NULL 1 clerk_role supervisor_role 2
Displays the roles granted to intern_role and the roles above it in the hierarchy:
sp_displayroles "intern_role", expand_up
Shows the login security-related parameters configured for the specified role:
sp_displayroles physician_role, "display_info"
Role name = physician_role Locked : YES Date when locked: Jul 14 2007 9:15AM Reason: Role locked by Adaptive Server due to failed login attempts reaching max failed logins. Locking suid: dr_john Date of Last Password Change : Oct 31 1999 3:33PM Password expiration interval = 5 Password expired : NO Minimum password length = 4 Maximum failed logins = 10 Current failed logins = 3 Password encryption version: SHA-256
Displays the roles granted to login “tom,” which is associated with the login profile named “sec_profile”:
grant role sec_role to sec_profile create login tom with password C0mp13x login profile sec_profile grant role emp_role to tom go sp_displayroles tom go
Role Name Grantee ------------------------------------------------- emp_role tom sec_role sec_profile
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.
The permission checks for sp_displayroles differ based on your granular permissions settings.
Granular permissions 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. |
Granular permissions 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. |
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 |
|
Documents See “User-Defined Login Security” in the System Administration Guide for more information.
Commands alter role, create role, drop role, grant, revoke, set
System procedures sp_activeroles, sp_displaylogin, sp_helprotect
