Reports encryption information.
sp_encryption help | helpkey
sp_encryption help | helpkey [, key_name | wildcard] [, all_dbs | key_copy | display_cols]
sp_encryption help | helpkey [, system_encr_passwd] [, display_keys]
sp_encryption helpcol [, table_name | column_name ]
sp_encryption helpuser [, user_name | wildcard ][, key_copy]
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.keyname. When run by a non-privileged user, this command will list keyname, keyowner and keytype.
included for backward compatibility. Includes the same output as helpkey
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).
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.
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 the keys and key copies that are 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
used with system_encr_passwd to display the keys and key copies that are encrypted using the system encryption password.
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 Type of Password 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 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 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 Type of Password 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 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 Type of Password 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 Symmetric 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 Type of Password Key Recovery #of Key Copies ------------------------ ---------- ----------- ------------------- ----- ---------- --------------- ------ -------------- keydb.dbo.cc_key 256 AES Symmetric default key 1 1 System EncrPasswd 0 0 keydb.dbo.sample_key1 128 AES Symmetric key 0 0 System Encr Password 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 Type of Password Key Recovery #of Key Copies ------------------------ ---------- ----------- ------------------- ----- ---------- --------------- ------ ----- keydb.dbo.cc_key 256 AES Symmetric default key 1 1 System EncrPasswd 0 0 keydb1.tinnap.tinnap_key 128 AES Symmetric key 0 1 System Encr Passwd 0 0
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 example 2):
sp_encryption helpkey, tinnap_key1, key_copy
Owner.Keyname Assignee Type of Password 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 Type of Password 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 Type of Password --------------- -------------------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 Type of Password --------------- -------------------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 Type of Password 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 Type of Password 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
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.
