In SQL Server, there are three database roles that have CONTROL certificate permissions. Those are db_owner, db_DDLadmin and db_securityadmin.

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 ?

  • I've just come across this problem and am looking for the same solution. Did you ever find a way to block this? – Andrew Lackenby Nov 5 '18 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.