create procedure sys.sp_MSrepltrigpreparecert
(
@mode tinyint -- 1 = create/refresh, 2 = drop
,@certname sysname -- certificate name
,@publisher sysname -- publisher
,@publisher_db sysname -- publisher db
,@publication sysname -- publication
)
with execute as 'dbo'
as
begin
set nocount on
declare @retcode int
,@certpwd sysname
,@cmd nvarchar(4000)
,@objectname nvarchar(517)
,@object_id int
,@ccertname sysname
,@mssql_data_path nvarchar(255)
-- validate @mode
if (@mode not in (1,2))
return 1
-- Do we already have a certificate
select distinct @ccertname = c.name
from sys.crypt_properties cp join sys.certificates c
on cp.thumbprint = c.thumbprint
where object_name(cp.major_id) in
(select object_name
from dbo.MSreplication_objects
where upper(publisher) = upper(@publisher)
and publisher_db = @publisher_db
and publication = @publication
and object_type = 'T')
if (@ccertname is not null)
begin
-- if we are in drop mode, the name should match
if @mode = 2 and @certname != @ccertname
return 1
end
-- declare a cursor for
-- selecting the triggers for this publication
declare #hctriggers cursor local fast_forward for
select quotename(schema_name(o.schema_id)) + N'.' + quotename(o.name), o.object_id
from dbo.MSreplication_objects as ro
join sys.objects as o
on ro.object_name = o.name
and upper(ro.publisher) = upper(@publisher)
and ro.publisher_db = @publisher_db
and ro.publication = @publication
and ro.object_type = 'T'
-- process drop first
if (@ccertname is not null)
begin
open #hctriggers
fetch #hctriggers into @objectname, @object_id
while (@@fetch_status = 0)
begin
-- if the trigger is signed with certificate - drop it
if exists (select cp.thumbprint
from sys.crypt_properties cp join sys.certificates c
on cp.thumbprint = c.thumbprint
where c.name = @ccertname
and cp.major_id = @object_id)
begin
select @cmd = N'drop signature from ' + @objectname + N' by certificate ' + quotename(@ccertname)
exec (@cmd)
if (@@error != 0)
return 1
end
-- get the next trigger
fetch #hctriggers into @objectname, @object_id
end
close #hctriggers
-- drop the certificate
if cert_id(@ccertname) is not null
begin
select @cmd = N'drop certificate ' + quotename(@ccertname)
exec (@cmd)
if (@@error != 0)
return 1
end
end -- if (@ccertname is not null)
-- if drop mode - we are done
if (@mode = 2)
begin
deallocate #hctriggers
return 0
end
-- create the certificate
select @certpwd = cast(newid() as sysname) + '~Repl~' + cast(newid() as sysname)
select @cmd = N'create certificate ' + quotename(@certname)
+ N' encryption by password = ''' + @certpwd + N''' '
+ N' with subject = ''Replication certificate for sync trigger'' '
exec (@cmd)
if (@@error != 0)
return 1
-- select the triggers for this publication
open #hctriggers
fetch #hctriggers into @objectname, @object_id
while (@@fetch_status = 0)
begin
-- sign the trigger with certificate
select @cmd = N'add signature to ' + @objectname
+ N' by certificate ' + quotename(@certname) + N' with password = ''' + @certpwd + N''' '
exec (@cmd)
if (@@error != 0)
return 1
-- get the next trigger
fetch #hctriggers into @objectname, @object_id
end
close #hctriggers
deallocate #hctriggers
-- done with signing
-- drop the private key of the certificate
select @cmd = N'alter certificate ' + quotename(@certname) + N' remove private key '
exec (@cmd)
if (@@error != 0)
return 1
-- backup certificate for import
-- Note -if we can copy certificates across databases - this will not be needed anymore
exec @retcode = sys.sp_MSget_setup_paths @data_path = @mssql_data_path output
if (@retcode != 0 or @@error != 0 or @mssql_data_path = N'')
return 1
select @cmd = N'backup certificate ' + quotename(@certname) + N' to file = ''' + sys.fn_replreplacesinglequote(@mssql_data_path + N'\DATA\' + @certname) + '.cer'' '
exec (@cmd)
if (@@error != 0)
return 1
-- all done
return 0
end