Roles must be active to have access privileges (that is, inactive roles do not have privileges). A default role is activated during login. Roles with passwords are always inactive at login. If the activation predicate on a default role evaluates to false during login, the role is silently ignored and remains inactive.
To activate or deactivate a role:
set role role_name [with passwd "password"] {on | off}
Include the with passwd parameter only if you are activating role. See the Reference Manual: Commands.
For example, to activate the “financial_analyst” role with the password “sailing19”, enter:
set role financial_analyst with passwd "sailing19" on
If the role was granted using an activation predicate, Adaptive Server evaluates the predicate at this time. If the predicate evaluates to true, the role is enabled; otherwise, the role remains inactive and the server returns an error message.
Activate roles only when you need them, and deactivate them when the roles are no longer necessary. Keep in mind that, when the sa_role is active, you assume the identity of database owner within any database that you use.
