create procedure sys.sp_MSdropmergepalrole(@pubid uniqueidentifier = NULL)
AS
declare @retcode int
if (@pubid is NULL)
begin
declare hC1 CURSOR LOCAL FAST_FORWARD FOR select pubid FROM dbo.sysmergepublications FOR READ ONLY
OPEN hC1
FETCH hC1 INTO @pubid
WHILE (@@fetch_status <> -1)
BEGIN
EXECUTE @retcode = sys.sp_MSdropmergepalrole @pubid
if @retcode<>0 or @@error<>0
return 1
FETCH hC1 INTO @pubid
END
CLOSE hC1
DEALLOCATE hC1
end
-- drop the database role that contains users who have access to the publication
-- this has to be done outside the transaction since role operations cannot be
-- done in transaction
declare @role sysname
declare @member sysname
select @role = sys.fn_MSmerge_GetPALRole(@pubid)
if exists (select * from sys.database_principals where name=@role and type = 'R')
begin
-- this role is a memeber of the database pal role. Drop it from there.
if exists (select * from sys.database_principals where name='MSmerge_PAL_role' and type = 'R')
begin
exec @retcode = dbo.sp_droprolemember 'MSmerge_PAL_role', @role
if (@retcode <> 0 or @@error <> 0)
return 1
end
declare role_members cursor fast_forward
for select u.name as MemberName
from sys.database_principals u, sys.database_principals g, sys.database_role_members m
where g.name = @role
and g.principal_id = m.role_principal_id
and g.type = 'R'
and u.principal_id = m.member_principal_id
open role_members
fetch role_members into @member
while @@fetch_status <> -1
begin
exec @retcode = sys.sp_droprolemember @role, @member
if (@retcode <> 0 or @@error <> 0)
BEGIN
close role_members
deallocate role_members
RAISERROR (14005, 16, -1)
RETURN (1)
END
fetch role_members into @member
end
close role_members
deallocate role_members
exec @retcode = sys.sp_droprole @role
if (@retcode <> 0 or @@error <> 0)
BEGIN
RAISERROR (14005, 16, -1)
RETURN (1)
END
end