Downgrading from 15.0.2 Adaptive Server to 15.0 ESD#2 or later

Drop all key copies. Use sp_encryption to list the key copy assignee, the key name, owner, type of password, and whether the key copy is for recovery:

sp_encryption helpuser, null, key_copy

For example, to drop the key copies for key k1:

alter encryption key k1 drop encryption 
for user 'joe'
alter encryption key k1 drop encryption 
for user 'tinnap'

To drop the recovery key copy, execute:

alter encryption key k1 drop encryption for recovery

Modify any keys encrypted by explicit passwords to reencrypt them with the system encryption password. For example, if key k1 was created or altered for encryption using the password “rur2d2”, enter:

alter encryption key k1 with passwd "rur2d2" 
modify encryption with passwd system_encr_passwd 

Remove decrypt default attribute from encrypted columns. Use sp_help to determine whether a column has a decrypt default (for example, sp_help employee).

The output of sp_help indicates the internal names of any decrypt defaults and the column to which it applies.

For example, remove the decrypt_default from employee.salary, using:

alter table employee replace salary drop decrypt_default

Decrypt any columns whose datatype is not supported for encryption in versions earlier than 15.0.2. These include columns of type datetime, money, bigint, bit, unichar, univarchar, smalldatetime, smallmoney, date, time, and unsigned bigint. The output of sp_help indicates the types of the columns that are marked as encrypted.

For example, decrypt the salary column as:

alter table employee modify salary decrypt

Run the following command in each database:

sp_helprotect keycustodian_role

Revoke any privileges granted to keycustodian role:

revoke <permission> from keycustodian_role

For every user, run this in every database:

sp_displayroles <username>

Revoke keycustodian_role from any users to whom it was granted:

revoke role keycustodian_role from <user1>, <user2>, ... 

Running this command does not affect ownership of keys. Any users who were not previously explicitly granted create encryption key permissions cannot create new keys unless the SSO grants create encryption key to them.

If you have increased the size of the system encryption password to more than 64 bytes in version 15.0.2, you must modify it back to a pre-15.0.2 length. Enter:

sp_encryption system_encr_passwd 'old...long...password....', 
'new...shorter...password' 

Disable restricted decrypt permission:

sp_configure 'restricted decrypt permission', 0