cannot drop certificate

  • im trying to drop all certificates on my database, and then the master key, but cannot do this as there are objects encrypted by one particular cert that i called fcert. I done this ages ago and cannot remember what i encrypted with this cert. to drop the master key i have to drop the cert and to drop the cert i have to make sure no objects are encrypted by it. how is this achieved?

  • This might help.

    I ran a trace on SSMS and got the SQL out of it. I changed it a bit to include the OBJECT_NAME lookup.

    SELECTsigs.[name] as [NameOfCertificate],

    obj.crypt_type_desc as [SignatureBy],

    '[' + schemas.[name] + '].[' + objects.[name] + ']' as objectSigned,

    objects.type_desc

    FROM(SELECT name, thumbprint

    FROM sys.asymmetric_keys

    UNION

    SELECT name, thumbprint

    FROM sys.certificates) AS sigs

    INNER JOIN sys.crypt_properties AS obj

    ON sigs.thumbprint = obj.thumbprint

    INNER JOIN sys.all_objects objects

    ON objects.object_id = obj.major_id

    INNER JOIN sys.schemas schemas

    ON schemas.schema_id = objects.schema_id

    ORDER BY sigs.[name]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply