A system security officer can change the default role for any user. Individual users can change only their own default settings.
When a user logs in to Adaptive Server, the user’s roles are not necessarily active, depending upon how the role is set up as a default role. If a role has a password associated with it, the user must use the set role command to activate the role.
The system security officer or user determines whether to activate any roles granted by default at login. sp_modifylogin sets the default status of user roles individually for each user. sp_modifylogin only affects user roles, not system roles.
By default, user-defined roles that are granted are not activated at login, but system roles that are granted are automatically activated, if they do not have passwords associated with them.
To set up a role to activate at login:
sp_modifylogin loginname, "add default role", role_name
To assign more than one default role to a user, use multiple sp_modifylogin commands.
To ensure that a role is inactive at login:
sp_modifylogin loginname, "drop default role", role_name
For example, to change the default setting for Ralph’s intern_role to be active automatically at login, execute:
sp_modifylogin ralph, "add default role", intern_role
