在将MS-SQL Server 2005 Express Edition升级到MS-SQL Server 2005 Enterprise Edition的最后一幕中，我遇到了此错误：

The certificate cannot be dropped because one or more entities are either signed or encrypted using it. To continue, correct the problem...

因此，如何查找和解耦使用此证书签名/加密的实体，以便删除证书并继续进行升级？

我还期望/假设升级设置将提供一个新证书，然后将其与以前的实体重新耦合，否则我将不得不在设置后强行这样做。

解决方案

回答

Microsoft论坛具有以下代码伪装，可以删除证书：

use msdb   
BEGIN TRANSACTION
declare @sp sysname
declare @exec_str nvarchar(1024)    
declare ms_crs_sps cursor global for select object_name(crypts.major_id) from sys.crypt_properties crypts, sys.certificates certs where crypts.thumbprint = certs.thumbprint and crypts.class = 1 and certs.name = '##MS_AgentSigningCertificate##'    
open ms_crs_sps    
fetch next from ms_crs_sps into @sp    
while @@fetch_status = 0  
begin    
if exists(select * from sys.objects where name = @sp) begin print 'Dropping signature from: ' + @sp set @exec_str = N'drop signature from ' + quotename(@sp) + N' by certificate [##MS_AgentSigningCertificate##]'   
Execute(@exec_str)
if (@@error <> 0)
begin
declare @err_str nvarchar(1024)
set @err_str = 'Cannot drop signature from ' + quotename(@sp) + '. Terminating.'
close ms_crs_sps
deallocate ms_crs_sps
ROLLBACK TRANSACTION
RAISERROR(@err_str, 20, 127) WITH LOG
return
end
end
fetch next from ms_crs_sps into @sp
end
close ms_crs_sps
deallocate ms_crs_sps
COMMIT TRANSACTION
go

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3876484&SiteID=17

我尚未尝试过该脚本，因此请在尝试之前备份数据和系统，并在此处更新结果。