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 Adaptive Server 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.
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
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
Displays the properties of all base encryption keys similar to %key1 in all available databases (only the SSO can run this command):
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 the properties of base encryption key sample_key1 in all available databases (only the SSO can run this command):
sp_encryption helpkey, sample_key1, all_dbs
Db.Owner.Keyname Key Length Key Algorithm Key Type Pad Init Vector Type of Password Key Recovery #of Key Copies ------------------------ ---------- ----------- ------------------- ----- ---------- --------------- ------ ----- keydb.dbo.sample_key1 128 AES Symmetric key 0 0 System Encr Password 1 4 keydb1.dbo.sample_key1 192 AES Symmetric key 1 1 Login Passwd 1 2
Displays all the user access copies of keys when run by the SSO, key custodian, or DBO in the current database:
sp_encryption helpkey, Null, 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 dbo.sample.key1 tinnap Login Access 0 dbo.sample.key1 joesmp Login Passwd 1
When user “tinnap” runs this command, it displays the key copies assigned to this user and the key copies for the keys “tinnap” owns in the current database:
sp_encryption helpkey, Null, 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 dbo.sample.key1 tinnap Login Access 0
Displays all the user access copies of keys with name similar to “sample%” when run by the SSO, key custodian, or DBO:
sp_encryption helpkey, "sample%", key copy
Owner.Keyname Assignee Type of Password Key Recovery ------------------ ---------- --------------- ---------- dbo.sample_key1 tinnap Login Access 0 dbo.sample_key1 joesmp Login Passwd 1
When user “tinnap” runs this command, it displays the key copies of keys with names similar to “sample%” assigned to user “tinnap,” and the key copies for keys with names similar to “sample%” for which “tinnap” is the owner in the current database:
sp_encryption helpkey, "sample%", key copy
Owner.Keyname Assignee Type of Password Key Recovery ------------------ ---------- --------------- ---------- dbo.sample_key1 tinnap Login Access 0
When run by the SSO, key custodian, or the DBO, displays all key copies for key tinnap_key1 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 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
When run by the SSO, displays all encrypted columns in all available databases encrypted by keys in the current database:
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 testdb tinnap t4 c4 sample_key1 dbo coldb dbo t1 c1 sample_key1 dbo coldb billyg t2 c2
When this statement is run by user “tinnap,” Adaptive Server displays the columns in the current database encrypted by keys in the current database:
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
When run by the SSO, displays all encrypted columns in all available databases encrypted by a key with a name like “%key%” in the current database:
sp_encryption helpkey, "%key%", display_cols
Key Name Key Owner Database Name Table Owner Table Name Column Name ---------- --------- ------------- ---------- ---------- ---------- tinnap_key1 tinnap testdb tinnap t4 c4 sample_key1 dbo coldb dbo t1 c1 sample_key1 dbo coldb billyg t2 c2
When this statement is run by user “tinnap,” Adaptive Server returns all columns that are encrypted by keys with name matching “%key%” in the current database:
sp_encryption helpkey, "%key%", display_cols
Key Name Key Owner Database Name Table Owner Table Name Column Name ---------- --------- ------------- ---------- ---------- ---------- tinnap_key1 tinnap testdb tinnap t4 c4
This example displays all columns which have been encrypted by key sample_key1 across all available databases:
sp_encryption helpkey, sample_key1, display_cols
Key Name Key Owner Database Name Table Owner Table Name Column Name ---------- --------- ------------- ---------- ---------- ---------- sample_key1 dbo coldb dbo t1 c1 sample_key1 dbo coldb billyg t2 c2
When run by user “tinnap,” displays all columns in the current database that are encrypted by key sample_key1:
sp_encryption helpkey, sample_key1, display_cols
Key Name Key Owner Database Name Table Owner Table Name Column Name ---------- --------- ------------- ---------- ---------- ---------- sample_key1 dbo coldb dbo t1 c1 sample_key1 dbo coldb billyg t2 c2
When run by the SSO, key custodian, or DBO, lists keys and key copies that are encrypted with the system encryption password in the current database:
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 user “tinnap,” this command displays the keys owned by, or key copies assigned to, user “tinnap” that are encrypted with the system encryption password in the current database:
sp_encryption helpkey, system_encr_passwd, display_keys
Owner.Keyname Assignee --------------- ------------- dbo.sample_key1 tinnap
Lists all base keys owned by users in the current database when the SSO, key custodian, or DBO runs this command:
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 Passw d
When run by the SSO, key custodian, or DBO, lists all base encryption keys owned by user “tinnap” in the current database:
sp_encryption helpuser, tinnap
Owner.Keyname Type of Password --------------- -------------------tinnap.tinnap_key System Encr Passwd tinnap.tinnap_key1 User Passwd
If run by user “tinnap,” lists all base encryption keys owned by user “tinnap” in the current database:
sp_encryption helpuser, tinnap
Owner.Keyname Type of Password --------------- -------------------tinnap.tinnap_key System Encr Passwd tinnap.tinnap_key1 User Passwd
When run by the SSO, key custodian, or DBO, lists all key copies assigned to 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 user “tinnap” runs this statement, it displays the key copies assigned to this user and the key copies for the keys owned by this user 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 tinnap.tinnap_key1 samcool User Passwd 1 tinnap.tinnap_key1 billyg User Passwd 0
When run by the SSO, key custodian, or DBO, lists all the key copies in the current database with assignee names like “%na%”:
sp_encryption helpuser, '%na%', 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
When run by user “tinnap,” lists all the key copies in the current database with assignee name like “%na%” and the key copies for keys owned by this user with name like “%na%” only if the user’s name matches the wildcard pattern:
sp_encryption helpuser, '%na%', 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 1
When run by the SSO, key custodian, or DBO, lists all encrypted columns in the current database (coldb in this example) and the keys used to encrypt the columns:
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
When user tinnap runs this statement in the coldb database, Adaptive Server displays values for keyid instead of keyname for those keys not in coldb:
sp_encryption helpcol
Owner.Table.Column Db.Owner.Keyname ----------------------- --------------------- dbo.t1.c1 keydb1.123456 billyg.t2.c2 keydb.2345678 tinnap.t3.c3 coldb.dbo.sample_key3
When run by the SSO, lists all encrypted columns in table t3 in the current database, and the keys used to encrypt the columns across all available databases:
sp_encryption helpcol, t3
Owner.Table.Column Db.Owner.Keyname ----------------------- --------------------- tinnap.t3.c3 coldb.dbo.sample_key2
When run by user “tinnap,” lists all encrypted columns in table t3 in the current database and the keys used to encrypt the columns:
sp_encryption helpcol, t3
Owner.Table.Column Db.Owner.Keyname ----------------------- --------------------- tinnap.t3.c3 coldb.dbo.sample_key3
When run by the SSO, lists all encrypted columns named c1 in the current database across all available databases, and the keys used to encrypt the columns:
sp_encryption helpcol, c1
Owner.Table.Column Db.Owner.Keyname ----------------------- --------------------- dbo.t1.c1 keydb1.dbo.sample_key1
When run by user “tinnap,” lists all encrypted columns named c1 in the current database and the keyid of the keys used to encrypt the columns if the key is not present in the current database:
sp_encryption helpcol, c1
Owner.Table.Column Db.Owner.Keyname ----------------------- --------------------- dbo.t1.c1 keydb1.123456
When run by the SSO, lists all encrypted columns in table dbo.t1 in the current database and the keys used to encrypt the columns across all available databases:
sp_encryption helpcol, dbo.t1
Owner.Table.Column Db.Owner.Keyname ----------------------- --------------------- dbo.t1.c1 keydb1.dbo.sample_key1
When run by user “tinnap,” lists all encrypted columns in table dbo.t1 in the current database and the keyid of the keys used to encrypt the columns if the key is not present in the current database:
sp_encryption helpcol, dbo.t1
Owner.Table.Column Db.Owner.Keyname ----------------------- --------------------- dbo.t1.c1 keydb1.123456
When run by the SSO, lists all encrypted columns named c1 in table t1 in the current database and the keys used to encrypt the columns across all available databases:
sp_encryption helpcol, t1.c1
Owner.Table.Column Db.Owner.Keyname ----------------------- --------------------- dbo.t1.c1 keydb1.dbo.sample_key1
When run by user “tinnap,” lists all encrypted columns named c1 in table t1 in the current database and the keyid of the keys used to encrypt the columns if the key is not present in the current database:
sp_encryption helpcol, t1.c1
Owner.Table.Column Db.Owner.Keyname ----------------------- --------------------- dbo.t1.c1 keydb1.12345678
When run by the SSO, lists all encrypted columns named c1 in table t1 owned by the DBO in the current database, and the keys used to encrypt the columns across all available databases:
sp_encryption helpcol, dbo.t1.c1
Owner.Table.Column Db.Owner.Keyname ----------------------- --------------------- dbo.t1.c1 keydb1.dbo.sample_key1
When run by user “tinnap,” lists all encrypted columns named c1 in table t1 owned by the DBO, and the keyid of keys used to encrypt the columns if the key is not present in the current database:
sp_encryption helpcol, dbo.t1.c1
Owner.Table.Column Db.Owner.Keyname ----------------------- --------------------- dbo.t1.c1 keydb1.123456789
Which user runs sp_encryption determines its output. If a user without privileges runs sp_encryption helpkey, it reports the key_name, key_owner, and key_type for all base keys in the current database. When the SSO, key custodian, or DBO run sp_encryption helpkey, it lists these properties for all base keys in the current database: key name, key owner, key length, key algorithm, key type, pad, initialization vector, type of password used to encrypt the key, whether key recovery is enabled, and the number of key copies. The output is sorted on key_name and key_owner. key_type indicates if the key is symmetric and has the default property. The type of password can be a system encryption password or a user encryption password.
If you run sp_encryption helpkey and no keys are present in the database, it returns an informational message saying there are no keys in the current database.
When the SSO, key custodian, or DBO runs sp_encryption helpkey, key_name, it lists the base keys in the current database for key_name. If it is run by a user without privileges, it lists the key_owner, key_name and key_type for this key_name.
When the SSO runs sp_encryption helpkey, key_name, display_cols, it lists all columns across all available databases encrypted by key_name. If it is run by a user without privileges, it lists the columns in the current database encrypted by key_name.
If the SSO runs sp_encryption helpkey, key_name, display_cols and the key_name 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.
Only an SSO can run sp_encryption
helpkey, [,key_name | 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'.
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 key_name is NULL in sp_encryption helpkey, key_name, key_copy, it 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.
When a SSO, key custodian, or DBO runs sp_encryption helpkey, key_name, key_copy, it lists the key copies in the current database for key_name. If this is run by a user without privileges, it lists the key copies assigned to the user for that key_name and the key copies for that key_name 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.
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.
sp_encryption helpcol, column_name, 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 key_name 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.
