There are giant spiders at one point, so if you're arachnophobic it can be a problem. (close your eyes after the dragon breathes fire on you, and when you hear them talking about watching out for the whomping willow, open them again...yep I'm totally arachnophobic, which of course meant that I got to have a ride stop with my face right in one's face)
If huge Dementors scare you, that's another spot that's frightful.
A dragon breathes fire on you.
I've ridden it several times now, and about half the time I'm totally fine, and the other half I have to sit down and breathe for awhile while my face loses its green tinge from being ill. I've actually become OK with the tea party, but there are other rides that I can get a little funny-feeling on.
So I think it really varies.
The feeling of FLYING is so awesome, though, I refuse to give up trying it out.
Oh, and keep your head BACK.