To display the properties of all base encryption keys in the current database when run by the SSO, key custodian, or the DBO, issue:
sp_encryption helpkey
This is the output:
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
This is the output:
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%"
This is the output:
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 “tinnap” executes the command:
sp_encryption helpkey, sample_key1
this is the output:
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
This is the output:
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
This is the output:
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
This is the output:
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”
This is the output:
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”
This is the output:
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”
This is the output:
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”
This is the output:
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”
This is the output:
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”
This is the output:
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
This is the output:
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
This is the output:
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
This is the output:
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
This is the output:
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
This is the output:
Key Name Key Owner Database Name Table Owner Table Name Column Name ------------ ---------- --------------- ---------- ----------- ----------- sample_key1 dbo coldb dbo t1 c1sample_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
This is the output:
Key Name Key Owner Database Name Table Owner Table Name Column Name ------------ ---------- --------------- ---------- ----------- ----------- sample_key1 dbo coldb dbo t1 c1sample_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
This is the output:
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
This is the output:
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
This is the output:
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
This is the output:
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 base encryption keys owned by user “tinnap” in the current database:
sp_encryption helpuser, tinnap
This is the output:
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
This is the output:
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”
This is the output:
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”
This is the output:
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”
This is the output:
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”
This is the output:
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
This is the output:
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
This is the output:
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
This is the output:
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
This is the output:
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
This is the output:
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
This is the output:
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
This is the output:
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
This is the output:
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
This is the output:
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
This is the output:
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
This is the output:
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
This is the output:
Owner.Table.Column Db.Owner.Keyname ----------------------- --------------------- dbo.t1.c1 keydb1.123456789
When run by the SSO, lists the properties of the system encryption password in each database:
sp_encryption helpkey, system_encr_passwd, all_dbs
This is the output:
Database Type of system_encr_passwd Last modified by Date ---------- ---------------------------- ------------------ ------------------- master persistent sa Aug 26 2008 10:05AM
