In SQL Server, there are three database roles that have
CONTROL certificate permissions. Those are
Also, the documentation for
decryptbycert specifies that
CONTROL permission is needed to use the certificate. Anyone with control permissions can backup the certificate with the private key because it is automatically decrypted by the Database Master key. I've reviewed using database triggers, denying view definition (which disabled backups of the certificate, but also made the certificate unusable to the user) and other options to try preventing certificate backups to no avail.
I do understand that this can be accomplished by creating
certificateuser signed procedures that will allow a user to use the encryption without the control permissions on the certificate, but I need a more immediate resolution that works against the users in the above roles.
I want to know if anyone has any ideas or knows of any trace flags that can be used to reserve backing up keys and certificates to only members of
sysadmin or anything else that could work ?