When you specify a data for encryption, you can use a named key from the same database or from a different database. Encrypting with a key from a different database provides a security advantage because, in the event of the theft of a database dump, it protects against access to both keys and encrypted data.
Administrators can also protect each database dump with a different password, making unauthorized access even more difficult.
Encrypting with a key from a different database needs special care to avoid data and key integrity problems in distributed systems. Carefully coordinate database dumps and loads. If you use a named key from a different database, SAP recommends that, when you dump a database that contains:
Encrypted columns, you also dump the database where the key was created. You must do this if new keys have been added since the last dump.
An encryption key, dump all databases containing columns encrypted with that key. This keeps encrypted data in sync with the available keys.
If you do not specify a named key, the data is automatically encrypted with the default key from the same database. The system security officer or the key custodian can use sp_encryption to identify the columns encrypted with a given key.