----------------- ---------------------- ---------------------------
DECLARE @signerName sysname
SELECT @signerName = name FROM SYS.CERTIFICATES WHERE thumbprint =  0x6BBCCD3A0E061553E86B29C03923110A52800989
IF @signerName IS NOT NULL
BEGIN

	DECLARE @s nvarchar(1024)
	SELECT @s  = 'DROP SIGNATURE FROM DATABASE::' + db_name() + ' BY CERTIFICATE [MS_SchemaSigningCertificate57A22F32CEFA5D366AF102D1A06F097E2BCB592A] '
	EXECUTE(@s)
	IF (@@error <> 0)
	BEGIN
		RAISERROR('Cannot drop signatures by certificate [MS_SchemaSigningCertificate57A22F32CEFA5D366AF102D1A06F097E2BCB592A] ', 16, 1)
		RETURN
	END

	SELECT @s  = 'DROP CERTIFICATE [' + @signerName + '] '
	EXECUTE(@s)
	IF (@@error <> 0)
	BEGIN
		RAISERROR('Cannot drop certificate [MS_SchemaSigningCertificate57A22F32CEFA5D366AF102D1A06F097E2BCB592A] ', 16, 1)
		RETURN
	END
END
GO

DECLARE @master_file NVARCHAR(1024)
SELECT @master_file = physical_name FROM master.sys.database_files WHERE name = 'master'
DECLARE @wmsde_schema_path NVARCHAR(1024)
SET @wmsde_schema_path = REPLACE(@master_file, 'Data\master.mdf', '') + N'SchemaSig'

DECLARE @s nvarchar(1024)
SELECT @s  = 'CREATE CERTIFICATE [MS_SchemaSigningCertificate57A22F32CEFA5D366AF102D1A06F097E2BCB592A] FROM FILE = ''' + @wmsde_schema_path + '\' + 'wsrmsign2k8.cer'''
EXECUTE(@s)
IF (@@error <> 0)
BEGIN
	RAISERROR('Cannot create certificate from file wsrmsign2k8.cer', 16, 1)
	RETURN
END

SELECT @s  = 'ALTER CERTIFICATE [MS_SchemaSigningCertificate57A22F32CEFA5D366AF102D1A06F097E2BCB592A] ATTESTED BY ''' + @wmsde_schema_path + '\' + 'wsrmsign2k8.dll'''
EXECUTE(@s)
IF (@@error <> 0)
BEGIN
	RAISERROR('Cannot add attested DLL to certificate from file wsrmsign2k8.dll', 16, 1)
	RETURN
END
GO

-- step 4 - create the old signatures
DECLARE @s nvarchar(1024)
SELECT @s  = 'ADD SIGNATURE TO [dbo].[PROCINFO] BY CERTIFICATE [MS_SchemaSigningCertificate57A22F32CEFA5D366AF102D1A06F097E2BCB592A] WITH SIGNATURE = 0x5EA711016A1AEC4878527D35E3A595D515EB5141A5D4A4ADD9E8541BC7EE9BB7B762EAA69FDB5966DC6FD900A449507AA0C78817903CEBC67FBB25B2805870E853EDD1A4B69AF3E46286B54B80857C8058FB2D41CB6F2DCCD02F79DC1FAA57436929605B9637D5931B10B7059BD0024EFE8A54F36DF9F0D07ECDD693D4EAA967'
EXECUTE(@s)
GO

DECLARE @s nvarchar(1024)
SELECT @s  = 'ADD SIGNATURE TO [dbo].[DYNAINFO] BY CERTIFICATE [MS_SchemaSigningCertificate57A22F32CEFA5D366AF102D1A06F097E2BCB592A] WITH SIGNATURE = 0x2F08DEFBBFB2CFDC6D5B72F1583D3E350CB0FEE819B0CDC744F01C13146587E2A801C8FD8A9FD346D0F3B16B1E12593FB2EC60FF8B937EBAD3ED17C28F99728AFBA2D7C2E6843DB5BDE0CABA2CB7C7B2361CB90F1C939ABA0A2A355C9F742ED814CACB22DC826BBB06B526590FB6C0F9D1509BC52A73C09A4ADC51C2528D7C0F'
EXECUTE(@s)
GO
