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

Question

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];

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.

score 1 · Accepted Answer · answered Jun 05 '17 at 10:26

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

answered Jun 05 '17 at 10:26

Mazhar

823
4
11
24

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

1 Answers1