I had some success in scaring my players, though it doesn't happen often. Yet.
One time i was GMing a short Call of Cthulhu campaign, and besides scaring the hell out of two girls in party with a moving severed hand (i showed
how it rushed at them; future note: ask your players if they fear spiders, and such. Sometimes too much is far too much.), i had a really good idea with an old, evil, occult book. Well, there was one in the (official) story i was playing, with a note that it's probably too damaged by age and environment to use. So i decided to destroy that book in a scary, but not dangerous way.
One of the girls drom the 'hand incident' (boy, my ears ringed for the rest of the evening

) decided to enter the basement, where an evil cult was based, before the authorities took it out. She stumbled apon the pedestal on which the book was based... And rolled a critical success (02 to be specific) on Occult, to recognize the book. I started describing all those fearfull notes she found in other occult tomes, mentioning those dark secrets and evil rites that the book she just found contained. Described the eerie silence that befall and dar, damp athmosphere of the place (it was autumn, around 22:00, and her friends went around asking questions, so they weren't around). She reaches for the book... Fails her perception test...
And the book explodes into wet dust and gray, fast mooving shapes... (i use fast, almost spat out words to decribe it, no motions this time)
And she faints. Really... And when she comes to, we finish the game session with getting info and having the usual occult-undead-boss-fight, with a levitating knife, and things like that. And it didn't spoil the mood. I'd say that was a success.
The other time i managed to get my players (more experienced ones this time) scared was a World of Darkness game this spring (i think). I had them looking for a IIWW russian occult treasure transport reclaimed by Wehrwolf and a nazi occult task force. They found where it was and decided to enter the bunker complex. Making their stumbling around in the unnatural, thick mist and flickering lights that shouldn't work after over fifty years not getting dull and boring was a pain, but it seems to have worked. Especially that the thing that was inside tried to wor what it has got, and manipulated the wolf-blooded character's emotions and urges... Which he did surpress with great difficulty and good roleplaying. Finding twisted and preserved nazi soldier bodies (with a 'nazi zombie' hint - they encountered those already, as they look for things the modern nazi occultists want too), boxes full of seemingly intact, but rotten dollar bills, and the mist doing things it shouldn't helped make the final encounter scary enough, though they managed to fight the thing, and drive it into hiding...
Well, they also managed to set that thing free, by removing what is has guarded. So odds are, when they finally Awaken, they can meet it on terms that will make it an even fight.