0

SQL Server 2008r2

I'm trying to drop a certificate I've created but i get the following error

The certificate cannot be dropped because one or more entities are either signed or encrypted using it

Using this query I've identified the entities in question

SELECT SCHEMA_NAME(so.[schema_id]) AS [SchemaName],
       so.[name] AS [ObjectName],
       so.[type_desc] AS [ObjectType],
       ---
       scp.crypt_type_desc AS [SignatureType],
       ISNULL(sc.[name], sak.[name]) AS [CertOrAsymKeyName],
       ---
       scp.thumbprint
FROM sys.crypt_properties scp
INNER JOIN sys.objects so
        ON so.[object_id] = scp.[major_id]
LEFT JOIN sys.certificates sc
        ON sc.thumbprint = scp.thumbprint
LEFT JOIN sys.asymmetric_keys sak
        ON sak.thumbprint = scp.thumbprint
WHERE   so.[type] <> 'U'
AND ISNULL(sc.[name], sak.[name]) = 'Certificate_name'
ORDER BY [SchemaName], [ObjectType], [ObjectName], [CertOrAsymKeyName];

enter image description here

but when I try to drop the SIGNATUREs with the following statement:

DROP SIGNATURE FROM OBJECT::dbo.sp_sqlagent_notify BY CERTIFICATE Certificate_name

I get them following error.

A signature by certificate 'Certificate_name' does not exist.

What am I doing wrong?

Thanks in advance.

4
  • Post your query it seems there is a type.
    – McNets
    Jun 5, 2017 at 10:00
  • I've added the query to the question
    – Mazhar
    Jun 5, 2017 at 10:02
  • AND ISNULL(sc.[name], sak.[name]) = 'Certificate_name' what is this?
    – McNets
    Jun 5, 2017 at 10:03
  • limiting the query to find the entities signed by my certificate, "certificate_name" isnt the actual name of the certificate
    – Mazhar
    Jun 5, 2017 at 10:06

1 Answer 1

1

My bad.

I should be using

DROP COUNTER SIGNATURE FROM OBJECT::dbo.sp_sqlagent_notify BY CERTIFICATE certificate_name

instead of

DROP SIGNATURE FROM OBJECT::dbo.sp_sqlagent_notify BY certificate_name
1
  • You should accept this as an answer so that it won't appear in those questions for which answer is not appropriate or didn't work. Apr 14, 2019 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.