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],
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.

  • Post your query it seems there is a type. – McNets Jun 5 '17 at 10:00
  • I've added the query to the question – Mazhar Jun 5 '17 at 10:02
  • AND ISNULL(sc.[name], sak.[name]) = 'Certificate_name' what is this? – McNets Jun 5 '17 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 '17 at 10:06

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
|improve this answer|||||
  • 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. – Learning_DBAdmin Apr 14 '19 at 14:48

Your Answer

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

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