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.