Occasionally someone does get a bad colonoscopy, but it's pretty rare. And, in fact, most of the people that have to put up with the pain are people that can't be given sedatives because of other medical problems. Other than that, no one should be conscious during the procedure because it is painful, especially if they biopsy.
Make sure you take someone with you to the procedure to be your advocate. If, during the procedure, you experience any awareness or pain, tell them to knock you out. If they are slow or reluctant to do so, tell them to stop right now. If they don't stop, then they are in violation of your rights and you can sue for malpractice. You NEVER have to get a test done that you don't want done and you ALWAYS have the right to stop the test in the middle if you feel the need to.
If push comes to shove, start screaming for the person that brought you in. And, for good measure, add phrases like "Murder!" and "They're killing me; stop them!" and "I'm sueing all of you!". I assure you, they will either cease to cause you pain or get you knocked out in a real hurry if you start screaming things like that and causing a scene. Don't let anyone in the medical field abuse you. Sometimes pain is a necessary evil for treatment or testing, but, in this case, it's absolutely not.