If user Bill, who has key copies encrypted by his login password, loses his login password, you can recover his access to encryption keys with these steps:
The SSO uses sp_password to issue Bill a new login password. Adaptive Server drops any key copies assigned to Bill for login association or key copies already encrypted by Bill’s login password.
The key custodian follows the regular procedure for setting up key encryption by login association. He verifies that the system encryption password was set, and creates Bill’s key copy:
alter encryption key k1 with passwd 'masterofsecrets' add encryption for bill for login_association
This step assumes the key custodian still knows the base key’s password. If the key’s encryption password is unknown, the key custodian must first follow the key recovery procedure. See “Loss of password on base key” for more information.
The next time Bill accesses data encrypted by k1, Adaptive Server reencrypts Bill's key copy using Bill's new login password. For example, if emp_salary is encrypted by key k1, the following statement automatically reencrypts Bill’s key copy with his login password:
select emp_salary from emp where name like 'Prisicilla%'
