Bono sang backup for Robbie Robertson.
Axle Rose sang backup for Don Henley
Sting sang backup for Dire Straights
Michael Stipe sang backup for The Indigo Girls
John Lennon sang backup for Bowie
Don Henley sang backup for Cheryl Crow and Linda Ronsdat sang for Henley
Although she is not famous persay, Merry Clayton sang backup to Jagger on "Gimmer Shelter" and frankly stole the show. Without a doubt, the most powerful backup singer in any song I can think of.
As the story goes, the Stones invited Merry to sing backup on Gimme Shelter and she agreed to reherse with them and completely blew them away with her voice, but she refused to be recorded until she was paid in full (figuring probably correctly that once it was recorded, it would be like putting toothpaste back in the tube trying to get money out of the production company) Merry's singing can only be described as ferocious.