Reports encryption information.
sp_encryption help | helpkey
sp_encryption help | helpkey [, key_name | wildcard] [, all_dbs | key_copy | display_cols]
sp_encryption help | 'helpkey', {'master'|'dual master'} [, 'display_keys' | 'all_dbs'
sp_encryption 'help'[, 'servicekeyname '[, 'display_objs']]
sp_encryption 'helpextpasswd'
sp_encryption helpcol [, table_name | column_name ]
sp_encryption helpuser [, user_name | wildcard ][, key_copy | login_passwd_check ]
sp_encryption 'mkey_startup_file'[, {'new_path' | 'default_location' | 'null'} [, {sync_with_mem | sync_with_qrm}]]
sp_encryption 'downgrade_kek_size' [, 'true'|'false']
sp_encryption system_encr_passwd, 'newpasswd' [,'oldpasswd']
Whether the database contains encryption keys.
The following, when run by a user with sso_role, key custodian, or DBO: keyname, keyowner, key length, key algorithm, key type, pad, initialization vector, type of password used to encrypt the key, whether key recovery has been enabled and count of key copies. The output is sorted on owner.key_name. When run by a non-privileged user, this command lists keyname, keyowner and keytype.
The base key owner.
If the key copy is a recovery key copy.
The user to whom a copy belongs.
If the copy is encrypted with a user-encryption password, a login password, or the system encryption password for login association (indicated by Login Access).
You must be the system security officer, key custodian, or the database owner can run sp_encryption helpkey, master | 'dual master', display_keys to display encryption keys protected by the master or dual master key.
You must be the system security officer, key custodian, or the database to run sp_encryption helpkey, keyname, display_objs to display objects in current database protected by the syb_extpasswdkey or syb_syscommkey service keys.
FIPS Encryption – the password is protected by the syb_extpasswdkey service key using a FIPS compliant cryptography algorithm
Needs Reset – indicates the system removed the password, and you must manually reset it.
Legacy Encryption – the password is protected with an algorithm from a version of SAP ASE earlier than 15.7.
You must be the system security officer to run sp_encryption helpextpasswd to check the status of external passwords.
You must be the system security officer display, set, or sync the master key startup file.
You must be the system security officer display, set, or sync the master key startup file.
If you specify no argument, sp_encryption displays the current value for downgrade_kek_size.
You must be the system security officer or the key custodian to run this command.
The system encryption password has not been set for all available databases
1> create encryption key key1 as default for database encryption 2> go 1> sp_encryption helpkey, key1 Key Name Key Owner Key Length Key Algorithm Key Type Pad Initialization Vector Protected By Key Recovery # of Key Copies ----------- ------------ ------------ ------------- -------------------------------- ------ --------------------- -------------- ---------------- ----------------- key1 dbo 256 AES symmetric database encryption key 0 1 master key 0 0 1> create encryption key key2 for database encryption with master key 2> create encryption key key3 for database encryption with dual_control 3> go 1> sp_encryption helpkey, 'key%' Key Name Key Owner Key Length Key Algorithm Key Type Pad Initialization Vector Protected By Key Recovery # of Key Copies ----------- ------------ ------------ ------------- -------------------------------- ------ --------------------- -------------- ---------------- ----------------- key1 dbo 256 AES symmetric database encryption key 0 1 master key 0 0 key2 dbo 256 AES symmetric database encryption key 0 1 master key 0 0 key3 dbo 256 AES symmetric database encryption key 0 1 dual_control(master key + dual master key) 0 0 1> create database encr_db1 encrypt with key1 2> create database encr_db2 encrypt with key2 3> create database encr_db3 encrypt with key3 4> go 1> sp_encryption helpkey, '%', "display_dbs" Key Name Key Owner Encrypted Database -------------------- -------------------------------------- key1 dbo encr_db1 key1 dbo encr_db2 key3 dbo encr_db3
This displays properties of all base encryption keys in the current database when run by the SSO, key custodian, or the DBO:
sp_encryption helpkey
Key Name Key Owner Key Length Key Algorithm Key Type Pad Init Vector Protected By Key Recovery # of Key Copies ---------- --------- ---------- -------------- -------- ---- ----------- -------------------------- ------------ --------------- tinnap_key tinnap 128 AES symmetric key 0 1 system encryption password 0 0 tinnap_key1 tinnap 128 AES symmetric default key 0 1 user Passwd 1 3 sample_key1 dbo 192 AES symmetric key 1 1 login Passwd 1 2
When run by user “tinnap,” this displays the following properties of all base encryption keys in the current database:
sp_encryption helpkey
Key Name Key Owner Key Type -------------- --------- ------------ tinnap_key tinnap symmetric key tinnap_key1 tinnap symmetric default key sample_key1 dbo symmetric key
If you are not the system security officer, or do have keycustodian_role, the query displays all base keys you own in the current database. If you do not specify a user_name as the second parameter, the query displays the base keys you own.
sp_encryption helpkey, sample_key1
Key Name Key Owner Key Length Key Algorithm Key Type Pad Init Vector Protected By Key Recovery # of Key Copies --------- -------- --------- -------------- -------------- --- ----------- ------------ ------------- --------------- sample_key1 dbo 192 AES symmetric Key 1 1 Login 1 2
When non-privileged user “tinnap” runs this command, it displays the following properties for the base encryption key sample_key1 in the current database:
sp_encryption helpkey, sample_key1
Key Name Key Owner Key Type ------------- ----------- ------------ sample_key1 dbo ymmetric key
sp_encryption helpkey, NULL, all_dbs
Db.Owner.Keyname Key Length Key Algorithm Key Type Pad Init Vector Protected By Key Recovery #of Key Copies ------------------------ ---------- ----------- -------------------- ----- ---------- ----------------- -------------- ---------------- keydb.dbo.cc_key 256 AES symmetric default key 1 1 system encr passwd 0 0 keydb.dbo.sample_key1 128 AES symmetric key 0 0 system encr passwd 1 4 keydb1.tinnap.tinnap_key 128 AES symmetric key 0 1 system encr passwd 0 0 keydb1.tinnap.tinnap_key1 128 AES symmetric default key 0 1 user password 1 3 keydb1.dbo.sample_key1 192 AES symmetric key 1 1 login passwd 1 2
sp_encryption helpkey, '%key', all_dbs
Db.Owner.Keyname Key Length Key Algorithm Key Type Pad Init Vector Protected By Key Recovery #of Key Copies ------------------------ ---------- ----------- -------------------- ----- ---------- --------------- -------------- ---------------- keydb.dbo.cc_key 256 AES symmetric default key 1 1 system encr passwd 0 0 keydb1.tinnap.tinnap_key 128 AES symmetric key 0 1 system encr passwd 0 0
sp_encryption helpkey, "tinnap%"
Key Name Key Owner Key Length Key Algorithm Key Type Pad Init Vector Protected By Key Recovery # of Key Copies --------- -------- --------- -------------- ----------------------- --- ----------- ------------------- ------------ --------------- tinnap_key tinnap 128 AES symmetric key 0 1 system encr passwd 0 0 tinnap_key1 tinnap 128 AES symmetric default key 0 1 user passwd 1 3
When run by user “tinnap,” displays the following properties for the base encryption keys in the current database with names similar to “tinnap%”:
sp_encryption helpkey, "tinnap%"
Key Name Key Owner Key Type ------------------ --------- ------------ tinnap_key tinnap symmetric key tinnap_key1 tinnap symmetric default key
sp_encryption helpkey, tinnap_key1, key_copy
Owner.Keyname Assignee Protected by Key Recovery ------------------ --------- ---------------- ---------- tinnap.tinnap_key1 joesmp user passwd 0 tinnap.tinnap_key1 samcool user passwd 1 tinnap.tinnap_key1 billyg user passwd 0
When run by user “joesmp,” this displays all encryption key copies assigned to user “joesmp” and also all the key copies for that keyname if the user is the owner of the key in the current database:
sp_encryption helpkey, tinnap_key1, key_copy
Owner.Keyname Assignee Protected by Key Recovery ------------------ --------- ---------------- ------------ tinnap.tinnap_key1 joesmp user passwd 0
sp_encryption helpkey, null, display_cols
Key Name Key Owner Database Name Table Owner Table Name Column Name ---------- --------- ------------- ---------- ---------- ---------- tinnap_key tinnap testdb1 tinnap t3 c3 tinnap_key1 tinnap testdb1 tinnap t4 c4 sample_key1 dbo coldb dbo t1 c1 sample_key1 dbo coldb billyg t2 c2
sp_encryption helpkey, system_encr_passwd, display_keys
Owner.Keyname Assignee --------------- ------------- dbo.cc_key NULL dbo.sample_key1 NULL dbo.sample_key1 tinnap
sp_encryption helpuser
Owner.Keyname Protected by --------------- -------------------tinnap.tinnap_key system encr passwd tinnap.tinnap_key1 user passwd dbo.sample_key1 login passwd
If user “tinnap” runs this command, lists all base keys owned by this user in the current database:
sp_encryption helpuser
Owner.Keyname Protected by --------------- -------------------tinnap.tinnap_key system encr passwd tinnap.tinnap_key1 user passwd
sp_encryption helpuser, NULL, key_copy
Owner.Keyname Assignee Protected by Key Recovery --------------------- ----------- ---------------- ----------- dbo.sample_key1 tinnap login passwd 0 tinnap.tinnap_key1 joesmp user passwd 0 dbo.sample_key1 joesmp login passwd 1 tinnap.tinnap_key1 samcool user passwd 1 tinnap.tinnap_key1 billyg user passwd 0
sp_encryption helpuser, NULL, “key_copy”
Owner.Keyname Assignee Protected by Key Recovery --------------------- ----------- ---------------- --------- dbo.sample_key1 tinnap login passwd 0 tinnap.tinnap_key1 joesmp user passwd 0 tinnap.tinnap_key1 samcool user passwd 1 tinnap.tinnap_key1 billyg user passwd 0
sp_encryption helpcol
Owner.Table.Column Db.Owner.Keyname ----------------------- --------------------- dbo.t1.c1 keydb1.dbo.sample_key1 billyg.t2.c2 keydb.dbo.sample_key1 tinnap.t3.c3 coldb.dbo.sample_key2
sp_encryption helpcol, t3
Owner.Table.Column Db.Owner.Keyname ----------------------- --------------------- tinnap.t3.c3 coldb.dbo.sample_key2
sp_encryption helpkey, system_encr_passwd, all_dbs
Database Type of system_encr_passwd Last modified by Date -------- -------------------------- --------------- ---------------- master persistent sa Aug 26 2008 10:05AM
sp_encryption helpkey,'master',display_keys
Owner.Keyname Assignee ---------------- ---------- user1.key_dual NULL user1.key_mst NULL user4.key_dC_pwd NULL user4.key_dC_pwd user5 user4.key_dC_pwd user6 user4.key_dC_pwd KC_tdb1
sp_encryption mkey_startup_file
Msg 19956, Level 16, State 1: Procedure 'sp_encryption', Line 298: The current master key startup file is:'/sybase/release/ASE-150/init/ase_encrcols_mk_l157.dat'.
sp_encryption helpkey, "syb_syscommkey%", display_objs
Key Name Key Owner Database Name Object owner Object Name ------------ ------- ----------- ------------ ----------- syb_syscommkey_1234567890ab dbo testdb user1 sp_mysproc1 syb_syscommkey_abcdefghijkl123456 dbo testdb user1 sp_mysproc2_ syb_syscommkey_ABCDEF123456 dbo testdb user2 sp_mysproc3
The privileges granted to the user who runs sp_encryption determines the output. See for more information.
If you run sp_encryption helpkey and no keys are present in the database, you see an informational message.
You must specify the key_copy parameter to get information about key copies. If you do not specify the key_copy parameter, sp_encryption returns information only about base keys.
If keyname is NULL in sp_encryption helpkey, keyname, key_copy, lists all the key copies in the current database for a SSO, key custodian, or DBO. If it is run by a user without privileges, it lists all the key copies assigned to the user in the current database and all key copies of the keys owned by the user in the current database.
name – if sp_encryption finds no tables of this name, it looks for all columns of that name.
name.name – is owner.table. If sp_encryption finds no tables of this name, it looks for a single column named table.column.
name.name.name – is owner.table.name.
For all columns identified by these rules in the current database, sp_encryption displays column name along with the key used to encrypt the column.
The output for sp_encryption helpcol, column_name is owner.table.column and db.owner.keyname. The keyname is expressed as database.keyid when run by non-SSO users, and the key is present in a different database from the encrypted column. The result set is sorted by owner.table.column.
Only an SSO can run sp_encryption helpkey [, keyname | wildcard], all_dbs to get the properties of keys in all databases. If a user without the sso_role runs this command, they receive an “unauthorized user” error message. If no keys qualify the keyname or wildcard, the SAP ASE server returns a message stating 'There are no encryption keys (key copies) like keyname in all databases'.
When the SSO runs sp_encryption helpkey, keyname, display_cols, it lists all columns across all available databases encrypted by keyname. If it is run by a user without privileges, it lists the columns in the current database encrypted by keyname.
If the SSO runs sp_encryption helpkey, keyname, display_cols and the keyname value is NULL, it displays all encrypted columns across all available databases. When run by a user without privileges, it displays all encrypted columns in the current database.
If an SSO, key custodian, or DBO runs sp_encryption helpuser, user_name, key_copy without specifying a user_name and key_copy for the helpuser parameter, it lists all the base keys owned by all users in the current database. If sp_encryption is run by a user without privileges without specifying a user_name or key_copy, it displays the base keys owned by the current user.
If any user runs sp_encryption helpuser, user_name, it lists all the base keys owned by owner.keyname. If a user without privileges runs the command and owns no base keys, the SAP ASE server displays an informational message stating this.
If an SSO, key custodian, or DBO runs sp_encryption helpuser, user_name, key_copy, it lists the key copies assigned to user_name. If a user without privileges issues this command, its lists the key copies assigned to this user and all the key copies of the keys owned by the user in the current database, with these columns in the result set: Owner.Keyname, Assignee, Type of Password, and Key Recovery. The output is sorted by Assignee.
If user_name is NULL for sp_encryption helpuser user_name, key_copy, it lists all the key copies in the current database for a SSO, key custodian, or DBO. For users without privileges, it lists all the key copies assigned to the user in the current database and the key copies for the keys owned by this user.
When a SSO, key custodian, or DBO runs sp_encryption helpkey, keyname, key_copy, it lists the key copies in the current database for keyname. If this is run by a user without privileges, it lists the key copies assigned to the user for that keyname and the key copies for that keyname if the user is the key owner.
The SSO, key custodian, and DBO can run sp_encryption helpkey, system_encr_passwd, display_keys to receive information on all keys and key copies in the current database encrypted by system encryption password. Users without privileges receive information about the base encryption keys or key copies they own or are assigned in the current database. Key copies are encrypted with the system encryption password only when they are created for login association. The output is sorted by owner.keyname.
The permission checks for sp_encryption differ based on your granular permissions settings.
Setting | Description |
---|---|
Enabled | With granular permissions enabled:
|
Disabled | With granular permissions disabled:
|