Scottish National Party's chief executive resigns over membership row