-- Name: sp_MSrepl_drop_all_role_members

-- Descriptions: Drop all role memebrs for spesific roles.

-- Parameters: as defined in create statement

-- Security:
--		Procedure is NOT PUBLIC so there are no security checks.

-- Returns: 0 - success
--          1 - Otherwise

CREATE procedure sys.sp_MSrepl_drop_all_role_members(@rolename sysname)
AS
	declare @member sysname
	declare @retcode  int

	declare #role_members cursor local fast_forward for
	select u.name
	from sys.database_principals u, sys.database_principals g, sys.database_role_members m
	where g.name = @rolename
		and g.principal_id = m.role_principal_id
		and g.type = 'R'
		and u.principal_id = m.member_principal_id
	for read only
    		
	open #role_members
	fetch #role_members into @member
	while @@fetch_status <> -1
	begin
	exec @retcode = sys.sp_droprolemember @rolename, @member
	if (@retcode <> 0 or @@error <> 0)
	BEGIN
		return (1)
	END
	fetch #role_members into @member
	end
	close #role_members
	deallocate #role_members

	return (0)