April 27, 2007 at 4:24 am
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?
October 30, 2009 at 8:19 am
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