USE [database name]
GO
 
DECLARE @RoleName sysname
set @RoleName = N'role name'
 
Begin
 
      DECLARE @RoleMemberName sysname
      DECLARE Member_Cursor CURSOR FOR
      select [name]
      from dbo . sysusers
       where uid in (
             select memberuid
            from dbo . sysmembers
            where groupuid in (
                  select uid
                  FROM dbo . sysusers where [name] = @RoleName AND issqlrole = 1 ))
 
      OPEN Member_Cursor ;
 
      FETCH NEXT FROM Member_Cursor
      into @RoleMemberName
 
      WHILE @@FETCH_STATUS = 0
      BEGIN
 
            exec sp_droprolemember @rolename = @RoleName , @membername = @RoleMemberName
 
            FETCH NEXT FROM Member_Cursor
            into @RoleMemberName
      END ;
 
      CLOSE Member_Cursor ;
      DEALLOCATE Member_Cursor ;
 
end
 
GO
DROP ROLE [rolename]
GO

Dropping a Role from a database

SQL