I think Oblivion came pretty close to doing this. Martin was the real hero of that story, as far as I'm concerned. Our characters functioned as his assistant for most of the game. At the conclusion of the story he "saves the day" while we stand by idly and watch.
When I play the Vilja companion mod I sometimes set her to be the leader and my character follows along. I find this a very fun way to play, despite technological limitations occasionally getting in the way. If a game were made in which our characters were truly the side-kick of a main character NPC, I would be very interested in playing it.
I know one thing: if I never hear or see another rancid "Chosen One" story it will be too soon for me.