create procedure sys.sp_unlink_publication_internal
(
@publisher sysname -- publishing server name
,@publisher_db sysname -- publishing database name
,@publication sysname -- publication name
,@reserved nvarchar(10)=null -- 'drop all for all subscriptions'
,@publisherlinkusertodrop sysname output -- name of the publisher link user to drop after this procedure completes (null if there is no need to drop any publisher link user)
)
as
begin
set nocount on
declare @retcode int
,@publisherlink sysname
,@publisherlinkuser sysname
,@rpcproc nvarchar(4000)
,@security_mode int
,@islocalpub bit
,@modedropall nvarchar(10)
,@certname sysname
-- initialize
select @publisher = upper(@publisher)
,@reserved = lower(@reserved)
,@islocalpub = case when (upper(@publisher) = upper(publishingservername())) then 1 else 0 end
,@modedropall = N'drop_all'
-- check if we need to unlink
if object_id('MSsubscription_properties') is not null
begin
if (@reserved = @modedropall)
begin
-- all subscriptions
-- unlink each subscription recursively
declare #hcsubprop cursor local fast_forward for
select distinct publisher, publisher_db, publication
from dbo.MSsubscription_properties
open #hcsubprop
fetch #hcsubprop into @publisher, @publisher_db, @publication
while (@@fetch_status != -1)
begin
exec @retcode = sys.sp_unlink_publication @publisher, @publisher_db, @publication
if (@retcode != 0 or @@error != 0)
return (1)
fetch #hcsubprop into @publisher, @publisher_db, @publication
end
close #hcsubprop
deallocate #hcsubprop
end -- @reserved = @modedropall
else
begin -- @reserved != @modedropall
-- specific subscription
-- Check if we need to do any processing
if object_id('MSsubscription_properties') is null
return 0
if not exists (select * from sys.columns where
object_id = object_id('MSsubscription_properties')
and name in ('publisherlink', 'publisherlinkuser'))
return 0
-- check for publisher link related information
select @publisherlink = case when (publisher_security_mode in (0,1))
then sys.fn_MSrepllinkname(N'REPLLINK', upper(@publisher), @publisher_db, @publication, db_name())
else publisherlink end
,@publisherlinkuser = case when (publisher_security_mode in (0,1) and publisherlinkuser = N'xxxx')
then N'repllinkproxy' else publisherlinkuser end
,@security_mode = publisher_security_mode
from dbo.MSsubscription_properties
where upper(publisher) = @publisher
and publisher_db = @publisher_db
and publication = @publication
if (@publisherlink is not null)
begin
-- skip this processing for cases
-- where MSreplication_objects does not exist
-- or we have an older version of MSreplication_objects
if object_id('MSreplication_objects') is null
return 0
if not exists (select * from sys.columns where
object_id = object_id('MSreplication_objects')
and name = 'publication')
return 0
-- we have link information
-- check if we need to remove certificates
select distinct @certname = 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) = @publisher
and publisher_db = @publisher_db
and publication = @publication
and object_type = 'T')
if (@certname is not null)
begin
-- remove cert grant from target db
if (@islocalpub = 0)
begin
select @rpcproc = N'master.sys.sp_MSrepltrigcertgrant'
exec @rpcproc @mode = 2
,@islocalpub = @islocalpub
,@certname = @certname
,@targetdb = N'master'
end
else
begin
select @rpcproc = quotename(@publisher_db) + N'.sys.sp_MSrepltrigcertgrant'
exec @rpcproc @mode = 2
,@islocalpub = @islocalpub
,@certname = @certname
,@targetdb = @publisher_db
end
-- remove cert from subscriber db
exec sys.sp_MSrepltrigpreparecert @mode = 2
,@certname = @certname
,@publisher = @publisher
,@publisher_db = @publisher_db
,@publication = @publication
end
if (@islocalpub = 0 and @security_mode in (0,1))
begin
-- remote publisher - drop unique link
exec @retcode = sys.sp_MSprocesspublisherlink @mode = 2
,@islocalpub = @islocalpub, @securitymode = @security_mode
,@linkname = @publisherlink, @remserver = @publisher
if @@error <> 0 or @retcode <> 0
return (1)
end
if (@security_mode = 0 and @publisherlinkuser is not null )
begin
-- remove context user if no other subscriptions are using it
if not exists (select *
from dbo.MSsubscription_properties
where publisherlinkuser = case when (@publisherlinkuser = N'repllinkproxy') then N'xxxx' else @publisherlinkuser end
and (upper(publisher) != @publisher
or publisher_db != @publisher_db
or publication != @publication))
begin
if exists (select * from sys.database_principals where name = @publisherlinkuser)
begin
set @publisherlinkusertodrop = @publisherlinkuser
end
end
end
-- update this entry in MSsubscription_properties
update MSsubscription_properties
set publisherlink = null
,publisherlinkuser = null
where upper(publisher) = @publisher
and publisher_db = @publisher_db
and publication = @publication
if (@@error <> 0)
return (1)
end -- if (@publisherlink is not null)
end -- @reserved != @modedropall
end -- if object_id('MSsubscription_properties') is not null
-- all done
return 0
end