It's hard to say because most the movies that scared the shit out of me was when I was young.
Nightmare on Elm Street - watched a few of these when I was young and they scared the shit out of me. Today they're more funny than scary but still love them.
Gothika - Saw this in theaters and I dunno why but it scared the living shit out of me and my sister. Watched it again at home when it came out on DVD and wasn't scary at all.
The Shining - still haven't brought myself to watch this but really want to.
Sinister - not a scary movie really, but man if you go watch it... there's some disturbing shit in there
Paranormal Activity - the first one was pretty well done, especially with the sound effects. The future ones were less effective because they used the same methods and the newest one was just terrible.