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 the master key or 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%'
