Getting Rid of a Certificate

A couple days ago I was playing around with some Event Notifications and the Certificate that I’d created for them on my development machine.  Low and behold I’d made a classic mistake and forgotten to write down the password for the certificate.  Thus I was left without a critical component necessary for signing my procedures.  Since this really wouldn’t do, I opted to drop the certificate from the server.

Now this shouldn’t be such a problem a siimple DROP CERTIFICATE statement and I should be good to go.  The statement I used look just like this:

 IF EXISTS(SELECT * FROM sys.certificates WHERE name = ‘MyCertificate’)
    DROP CERTIFICATE [MyCertificate]
GO 

Yup, Didn’t Work

There wouldn’t be much of a point to this if this worked as intended.  So here’s the rub, the statement ended up generating the following error:

Msg 15352, Level 16, State 1, Line 1
The certificate cannot be dropped because one or more entities are either signed or encrypted using it.

Backing up a bit, the reason that I created this certificate is that I’ve been using it to sign stored procedures.  I was doing that so that the procedure can execute under that a login that has the permissions I want rather that just opening a big security hole in my development machine.

What’s Signed By Your Certificate

To find out what’s signed by the certificate, you can use the sys.certificates and the sys.crypt_properties system views.  The query I use for this is below:

SELECT OBJECT_SCHEMA_NAME(co.major_id) + '.' + OBJECT_NAME(co.major_id)
FROM sys.certificates c 
    INNER JOIN sys.crypt_properties co ON c.thumbprint = co.thumbprint
WHERE co.crypt_type_desc = 'SIGNATURE BY CERTIFICATE' 
AND c.name = 'MyCertificate'

Hopefully, you won’t forget your certificate password like I did.  But you may need to remove a certificate from one of your systems some time and hopefully this get you passed that irritating error above.