No, max tension is at the bottom where T = mg + mv^2/R
Think of centripetal force as the force required to keep the object in circular motion. When the object is moving in circles, it's being pulled by the rope toward the center; otherwise it would just fly off tangentially. You are constantly tugging at the object to keep it from flying away tangentially.
More technically, the object in circular motion is accelerating; if you draw out two velocity vectors and subtract one from the other, you'll see the net acceleration toward the center as you described. But something needs to exert that force.
If you had a string that could break easily and you attached an object to it and released it like a pendulum, the string will probably break near the bottom since it's supporting both the circular motion as well as the weight of the object.