It happens so that when you drop/delete a Database Role in SQL Server Database, the Role members who were assigned to this role wont get de-associated from this Role. No doubt the role will be dropped/deleted but the member will be having this Role still existing in their permission list. So I came up with the below T-SQL which will first de-associate all the role member’s from the role to be dropped and then it will go ahead and drop the role. For Testing Purpose, I have taken Role “db_execute” as the role which needs to be dropped/deleted.

/* De-Associating RoleMember From Role To Be Dropped*/
USE [AdventureWorks]
GO
DECLARE @RoleName sysname
set @RoleName = N'db_execute'
IF  EXISTS (SELECT * FROM dbo.sysusers WHERE name = @RoleName AND issqlrole = 1)
Begin
 
      DECLARE @RoleMemberName sysname
      /* Cursor to Loop in for Each Member have the Role Privilege and Drop RoleMember */
      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 Of Cursor */
 
end
GO
/* Checking If Role Name Exists In Database */
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'db_execute' AND type = 'R')
DROP ROLE [db_execute]
GO