drop encryption key

Syntax

drop encryption key [[database.][owner].]keyname

The syntax for explicitly dropping an external login password service key is:

drop encryption key syb_extpasswdkey
	with password encryption downgrade

The syntax for explicitly dropping a hidden text service key is:

drop encryption key syb_syscommkey_dddddd

Or:

drop encryption key syb_syscommkey with text encryption downgrade

database – is the name of the database.
owner – is the owner.
keyname – is the name of the key.
syb_extpasswdkey – name of the service key
When you specify with password encryption downgrade, the SAP ASE server resets external login passwords with the algorithm used in versions earlier than 15.7, and the Replication Agent password, and the CIS and RTMS external login passwords are reset to an invalid value.

After the key is dropped, the administrator must reenter the passwords manually to resume using the corresponding services.
syb_syscommkey_ddddddd – is the explicit name of an individual syscomments service key to be dropped.
syb_syscommkey with text encryption downgrade – the SAP ASE server reencrypts all the hidden text in syscomments with the algorithm used in versions earlier than 15.7.

Example 1 – Drops the encryption key cc_key:
```
drop encryption key cust.dbo.cc_key
```

If the key has key copies, the copies are dropped along with the base key.
The command fails if:
- Any column in any database is encrypted using the key.
- The database encryption key you are dropping is still used to encrypt any database.
drop encryption key cannot check databases that are archived, suspect, offline, unrecovered, or currently being loaded for columns encrypted by the key. The command issues a warning message naming the unavailable database, but does not fail. When the database is brought online, any tables with columns that were encrypted with the dropped key are not usable. To restore the key, the system administrator must load a dump of the dropped key’s database from a time that precedes when the key was dropped.

See also sp_encryption and sp_help in Reference Manual: Procedures.

ANSI SQL – Compliance level: Transact-SQL extension.

The permission checks for drop encryption key differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be the key owner or a user with `manage any encryption key` privilege. For fully encrypted databases, SAP ASE creates a new permission called "`manage database encryption key`." You must have this permission to create a database encryption key.
Disabled	With granular permissions disabled, you must be the key owner or a user with sso_role. For fully encrypted databases, you must be a user with sso_role, keycustodian_role, or have create encryption key privilege.

Values in event and extrainfo columns of sysaudits are:

Information	Values
Event	109
Audit option
Command or access audited	drop encryption key
Information in `extrainfo`	Roles – current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – NULL Proxy information – original login name, if set proxy is in effect