I think the answer is that the second diagram you drew won't happen. I just picked up a string and tried this. What happened is that the first diagram is easy. For the second, I have to twirl the string faster, and I can't quite get it to stay above my hand. The best I can do is to get the mass to swing in a plane almost even with my hand.
Note: it's a little difficult to do this fairly with your hand. When I tried it, I had a tendency to slightly adjust the plane of motion of the mass, so that it oscillated slightly. This was particularly bad when trying the second situation. If I didn't do that, the sting would hit one of my knuckles every time it passed. That's another indication that the plane of revolution is actually below my hand.
Your force diagrams are qualitatively correct. Gravity points down towards the floor, and the tension points along the string at some angle. It's easiest to break the tension into a vertical component (which will either add to or subtract from gravity), and a radial component, which lies in the plane of the ball's orbit and provides the centripetal force.
To be concrete, take $\theta$ to be the angle between the string and the vertical. The string hanging under just gravity means $\theta = 0$, and the ball orbiting in the horizontal plane is $\theta = 90^\circ$. You want the vertical component to cancel out gravity, so $w = T\cos{\theta}$. As you increase $\theta$, $\cos{\theta}$ decreases, so you have to increase $T$ to keep the vertical component balanced. The centripetal force is $F_c = T\sin{\theta}$, which increases both because you increased $\theta$ and because you increased $T$. The ball will then need to move faster to account for the increased centripetal force. I'll let you work out the actual details, but you should get that the ball will need to move infinitely fast to get to $\theta = 90^\circ$.
An illustration of the wobble effect I noticed is when cowboys attempt to lasso animals. You can watch this video starting at 1:20 to see this.
The string is at a slight angle to horizontal $\theta$. It is not exactly horizontal. The slight angle is such that the tension in the string exactly counteracts gravity, $T\sin(\theta)=m g$. So, there is actually a force acting upwards that counteracts gravity, and it is supplied by the string.
You're right that if $\theta=0$ exactly, there would be a problem and the object would necessarily fall a bit.
Best Answer
The centripetal force can be made up of any type of force, whether gravitational, friction or tension. The centripetal force is not a force type, it is just a net force that is always radial. So it is a sum of forces, no matter the type.
So yes, it is a tension force. It just acts as a centripetal force.