I suggest that you support him no matter what and don't let on that you think he doesn't have what it takes. I realize that's not being completely honest, but hear me out.
If you tell him that he sucks and he gives up on his dream because of that, he will always wonder "what if?"--and you will forever be the person who killed his dream. You don't want that. If he's not superstar material, he will find out soon enough. In a creative field like music, just about everyone he meets will tell him he doesn't have what it takes. He will be discouraged at every turn. Let the bad news come from someone he doesn't love.
Also, are you a music talent scout? You say he's not great and you're probably right, but you never know who might see something in him that you don't. Musical talent isn't always necessary for stardom--just ask Ke$ha or T-Pain. So you never know. The odds are slim, but you never know.
He might get better, too. If he's serious about pursuing a career in music and throws himself into it, he could improve more than you think. Some performers are born, but many are made.
I say step back and let him pursue his dream. Even if he fails, he will have the satisfaction of knowing he gave it his best shot. Don't take that away from him.
Thanks for the question.