I think many atheists/agnostics would say that prayer offends them as opposed to scares them. Not the act of prayer in and of itself, but rather assuming that the atheist in question would want to be prayed for and would appreciate the gesture.

Personally, it depends on who's doing the praying and why. I think prayer is more about unloading your inner angst onto god/the divine/the universe rather than trying to change circumstances. If someone 'feels my pain' and wants to communicate that to what they consider the divine, then I am more likely to be grateful than offended. If however they are praying for me to see the light and accept the omniscience of a particular god, of course that's not going to go over well.

I looked at the blurb on amazon about the book Amos mentioned - it brings up the point that if prayer really does provide beneficial effects, can we ethically pray for someone without their consent? (We can't treat someone for a disease without their consent.)