Grants or revokes roles to an Adaptive Server login account.
sp_role {"grant" | "revoke"}, rolename, loginame
specifies whether to grant the role to or revoke the role from loginame.
is the role to be granted or revoked.
is the login account to or from which the role is to be granted or revoked.
Grants the System Administrator role to the login account named “alexander”:
sp_role "grant", sa_role, alexander
sp_role grants or revokes roles to an Adaptive Server login account.
When you grant a role to a user, it takes effect the next time the user logs into Adaptive Server. Alternatively, the user can enable the role immediately by using the set role command. For example, the command enables the System Administrator role for the user:
set role sa_role on
However, you must run sp_modifylogin to enable the login. For more information, see sp_modifylogin.
You cannot revoke a role from a user while the user is logged in.
When users log in, all system-defined roles that have been granted to them are active (on). To turn a role off, use the set command. For example, to deactivate the System Administrator role, use the command:
set role "sa_role" off
Only a System Administrator can execute sp_role to grant the System Administrator role to other users. Only a System Security Officer can execute sp_role to grant any role other than “sa” to other users.
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 |
|
Functions proc_role, show_role
System procedures sp_activeroles, sp_displayroles, sp_displayroles
