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.
