As another poster mentioned, I found the Nightmare on Elm Street films (well, the original at least) terrifying as a kid. I find Freddy funny now, the New Nightmare one I thought was pretty good.
As an adult these have spooked me -
The Changeling
Ju-On: The Grudge
The Shining (the TV mini, was never bothered by the Kubrick version).
And, weirdly - Silent Hill. It didn't give me sleepless nights but I found the tone of it really eerie and some of the monsters/ghosts were inventive.