Scary? I always found them more intriguing than scary. Ghosts can embody a certain kind of horror, however. For example, say sentient ghosts exist in some capacity but are all cut off from each other. They are more or less forced to walk the world alone for eternity perhaps. Sure they can watch over their loved one and generations later maybe, but that all ends eventually. And then what? What World War X breaks out and wherever you are is obliterated? Everyone dies around you while standing still, and you're faced to witness the horror of it all. Sure, repeated attempts may dull you in the long run, but it's still gonna be a lonely existence. I'd say the real horror is for the ghost and not the living.
Rationally, I know I should be more intrigued than scared. Rationally, I should WANT to see a ghost. And yes, rationally, if ghosts represent what to expect in the afterlife, being trapped in this world and yet unable to interact with it does stir up some existential horror. Personally, I tend to view ghosts as more of imprints on the environment than actually representations of the souls of the dead. Ghosts always seem to have a routine, always seem to ignore the world around them - so I've taken to the notion that if they are real, they're not actually spirits, but more sort of recordings.
That said, if I woke up and a ghost appeared in my room, I'd probably crap my pants in fear. Yes, I'd love to see a ghost. Yes, I'd know the ghost couldn't hurt me in any way, at least to the best of my knowledge. I mean hell, with seeing a ghost, there's a chance I could gather evidence from the encounter, and prove they're real and become famous and change the world or some bullshit like that. Even with all that, I'm still rather more happy in the knowledge that I will almost certainly not wake up to some ghastly apparition floating in my room. It's always been a funny paradox to me - that I'd love to see a ghost, hell, I grew up wanting to be a Ghostbuster, but realizing that if I ever got my wish, despite everything I know and believe, I'd be terrified beyond the capacity for rational thought.