-- 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)