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']
lists encryption key properties, including:
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.
included for backward compatibility. Includes the same output as helpkey
is the name of the key you are investigating. Lists the properties defined for key_name. If key_name is omitted, lists properties for all keys.
lists the properties for keys matching the wildcard pattern in the current database. See the Reference Manual: Building Blocks for information about using wildcards.
lists information on encryption keys in all available databases. Only the SSO can run all_dbs.
lists all user copies for the specified key in the current database. The output is sorted by key_owner.key_name. Includes information about:
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).
indicates if the key copies assigned to the matched users are well synchronized with the user’s login password. That is, the last update date of the key copy is newer than the date of the login password. The key copies are encrypted with the user’s login password or login association.
is used with system_encr_passwd to display the keys and key copies that are encrypted using the system encryption password. Used with master or dual master to display keys and key copies encrypted using the master key or the dual master key.
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.
displays the key name, all keys (or matching wildcard keys) in the current database and the columns the key encrypts. When SSO includes display_cols, it displays columns encrypted by the keys across all available databases. When a user without the sso_role runs display_cols, only those columns encrypted by the key in the current database are displayed. Data is sorted by key_name, key_owner, database_name, table_owner, table_name, and column_name.
reports information about the master key.
reports information about the dual master key.
is set to syb_extpasswdkey or syb_syscommkey%. Use with display_objs to display objects encrypted by the service key.
displays object owners.
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.
displays the encryption status of external passwords in the status column. The encryption status is one of:
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 Adaptive Server earlier than 15.7.
You must be the system security officer to run sp_encryption helpextpasswd to check the status of external passwords.
displays the column name and the key used to encrypt the column. If the SSO includes helpcol, it prints the key name even if the key is not present in the current database. If a non-SSO user includes helpcol, Adaptive Server prints the keyid of the key if it is not present in the current database, omitting the key_name. The output includes: owner.table.column, database.owner.keyname. The information is sorted by owner.table.column.
displays the keys owned by or assigned to a user in the current database.
displays or sets the master key startup file name and path. sp_encryption sets the master key startup file to new_path or the default location. If you specify null, or no location, sp_encryption displays the current master key startup file name and path.
(Cluster Edition only) writes the master key encryption key that exists in server memory to the master key startup file. Replaces the current master key encryption key, if it exists. If automatic master key access is set to off, sync_with_mem is also disabled.
You must be the system security officer display, set, or sync the master key startup file.
updates the local master key startup file with the version in the quorum device.
You must be the system security officer display, set, or sync the master key startup file.
displays or sets the downgrade kek size configuration. true indicates that Adaptive Server is in downgrade kek size mode, false disables this mode.
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.
displays the keys and key copies encrypted using the system encryption password in the current database.
displays the properties of the system encryption password in every database where it has been set. The output is sorted by database name. Only the system security officer an run this command. If the system encryption password has not been set for all databases, Adaptive Server generates Message 19782:
The system encryption password has not been set for all available databases
Use the helpkey parameter to display key information in the current database. You can get information on all keys or specific keys. The second parameter to sp_encryption supplies the key name and may include SQL pattern-matching characters. If you are not the database owner and do not have sso_role or keycustodian_role, sp_encryption displays fewer columns.
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.
Displays the properties of base encryption key sample_key1 when run by the SSO, key custodian, or DBO in the current database:
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
Displays the properties of all base encryption keys in all available databases (only the SSO can run this command):
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
all_dbs indicates that information on keys across all databases is required. You must have sso_role to use the all_dbs parameter.
Displays the properties of all base encryption keys similar to %key in all available databases:
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
Displays properties of all base encryption keys with names similar to “tinnap%” in the current database when run by SSO, key custodian, or DBO:
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
Displays information on key copies using key_copy as the third parameter. Enter null instead of value for keyname for the second parameter to see information on all key copies. You can use pattern-matching characters in keyname (see the previous example):
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
Use the display_cols parameter to show all encrypted columns in all available databases encrypted by keys from the current database. If you do not have the sso_role, the query displays only the encrypted columns in the current database encrypted by keys from the current database. You can use pattern matching characters or key_name for the second parameter. If you use pattern matching characters for key_name as sso_role, the query displays all encrypted columns in all available databases encrypted by the pattern matching key_name. If you use key_name for the second parameter and have the sso_role, displays all encrypted columns in all available databases encrypted by the specified key_name:
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
Displays all keys and key copies encrypted with the system encryption password in the current database. If you do not have these privileges, the query displays the keys owned by or assigned to the user which are encrypted with the system encryption password:
sp_encryption helpkey, system_encr_passwd, display_keys
Owner.Keyname Assignee --------------- ------------- dbo.cc_key NULL dbo.sample_key1 NULL dbo.sample_key1 tinnap
When run by the database owner or a user with keycustodian_role or sso_role, the helpuser parameter displays all base keys owned by users in the current database.
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
The database owner or a user with keycustodian_role or sso_role can use the key_copy parameter with the helpuser parameter to display key copies assigned to one or more users in the current database. You can use pattern-matching characters for the user parameter. This shows the key copies of all users in the current database:
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
If you are not the database owner and do not have keycustodian_role or sso_role, this query displays the copies of any keys you own and the key copies that other key owners have assigned to you. For example, when user “tinnap” runs this query:
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
If you are the database owner or a user with keycustodian_role or sso_role, helpcol displays all encrypted columns in the current database and the keys used to encrypt the columns. If you do not have these privileges, helpcol displays keyid instead of the key_name if the encryption key is in a different database:
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
Include the helpcol parameter with the table_name and column_name parameters to display all encrypted columns or a specific encrypted column in a given table. When run by a user with sso_role, the query below displays all encrypted columns in table t3 in the current database and the keys used to encrypt the columns across all available databases. When run by a user without sso_role, this query displays the key’s ID instead of its name if the key is not in the current database. The second parameter can have a combination of [database_name.][table_name.][column_name]:
sp_encryption helpcol, t3
Owner.Table.Column Db.Owner.Keyname ----------------------- --------------------- tinnap.t3.c3 coldb.dbo.sample_key2
Displays the system encryption password properties each database (you must have sso_role to run this query):
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
Displays all encryption keys encrypted with the master key in the current database (you must have sso_role, keycustodian_role, or be the database owner to run this query):
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
Displays the name and location of the current master key start-up file configured for the current server:
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'.
Displays three stored procedures are encrypted with key syb_syscommkey_123456, and are owned by user1 and user2:
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 “Usage restrictions” 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.
For sp_encryption helpcol, column_name uses the form name.name.name, where:
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, Adaptive 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, Adaptive 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.
Granular permissions enabled |
With granular permissions enabled: |
downgrade_kek_size – You must be a user with manage security configuration privilege. |
|
help/help_key system_encr_passwd, display_keys – You must be a user with manage column encryption key privilege. Any user can see their own key. |
|
help/help_key system_encr_passwd – You must be a user with manage column encryption key privilege. |
|
help/help_key master key/ dual master key, display_keys – You must be a user manage master key privilege. |
|
help/help_key keyname/wild card, display_cols – You must be a user with use any database privilege for cross database check. Any user for the current database. |
|
help/help_key service keyname, display_objs –You must be a user with manage service key privilege. |
|
help/help_key keyname/wild card, all_dbs – You must be a user with the following privilege depending the key type:
For cross-database checks, one of the above three, and use any database permission. |
|
help/help_key keyname wildcard – You must be a user with the following privilege depending the key type:
For non-privileged users, limited encyption key information is displayed. |
|
help/help_key keyname wildcard, key_copy – You must a user with the following privilege depending on the key type:
|
|
helpcol – You must be a user with use any database privilege for cross database checks. |
|
helpextpassword – You must be a user with manage service key privilege. |
|
helpuser username/wildcard, [key_copy/login_passwd_check] – You must be a user with manage any encryption key privilege. Non-privilege users can see their own key. |
|
mkey_startup_file – You must be a user with with manage security configuration privilege. |
|
system_encr_passwd – You must be a user with manage column encryption key privilege. |
|
verify_downgrade – You must be a user with manage security configuration privilege. |
|
Granular permissions disabled |
With granular permissions disabled: |
downgrade_kek_size – You must be a user with sso_role or keycustdian_role. |
|
help/help_key system_encr_passwd, display_keys – You must be the database owner, a user with sso_role, or a user with keycustdian_role. Any user can see their own key. |
|
help/help_key system_encr_passwd – You must be the database owner, a user with sso_role, or a user with keycustdian_role. |
|
help/help_key master key/ dual master key, display_keys – You must be the database owner, a user with sso_role, or a user with keycustdian_role. |
|
help/help_key keyname/wild card, display_cols – You must be a user with sso_role for cross database check. Any user for the current database. |
|
help/help_key service keyname, display_objs – You must be a user with sso_role or a user with keycustodian_role. |
|
help/help_key keyname/wild card, all_dbs – You must be a user with sso_role. |
|
help/help_key keyname wildcard – You must be the database owner, a user with sso_role, or a user with keycustdian_role. |
|
help/help_key keyname wildcard, key_copy – You must be the database owner, a user with sso_role, or a user with keycustdian_role. For:
|
|
helpcol – You must be a user with sso_role. |
|
helpextpassword – You must be a user with sso_role. |
|
helpuser username/wildcard, [key_copy/login_passwd_check] – You must be the database owner, a user with sso_role, or a user with keycustdian_role. Non-privilege users can see their own keys. |
|
mkey_startup_file – You must be a user with sso_role. |
|
system_encr_passwd – You must be a user with sso_role or keycustdian_role. |
|
verify_downgrade – You must be a user with sso_role or keycustdian_role. |
