Chapter 1: System Procedures

sp_role

Description

Grants or revokes roles to an Adaptive Server login account.

Syntax

sp_role {"grant" | "revoke"}, rolename, loginame

Parameters

grant | revoke: specifies whether to grant the role to or revoke the role from loginame.

rolename: is the role to be granted or revoked.

loginame: is the login account to or from which the role is to be granted or revoked.

Examples

Example 1

Grants the System Administrator role to the login account named “alexander”:

sp_role "grant", sa_role, alexander

Usage

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
```

Permissions

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.

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