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.
For 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.
