Roles must be active to have the access privileges of each role. Depending on the default set for a role, the role may or may not be active at login. If the role has a password, it will always be inactive at login.
To activate or deactivate a role:
set role role_name [on|off]
To activate or deactivate a role that has an attached password, use:
set role role_name with passwd "password" [on|off]
For example, to activate the “financial_analyst” role with the password “sailing19”, enter:
set role financial_analyst with passwd "sailing19" on
You should activate roles only when you need them and turn them off when you no longer need them. For example, when the sa_role is active, you assume the identity of Database Owner within any database that you use. To turn off the System Administrator role and assume your “real” user identity, use:
set role sa_role off
If you are granted a role during a session, and you want to activate it immediately, use set role to turn it on.
