All that said, I'm a huge fan of the conceit of a roleplaying game utilizing elements of the Cthulhu mythos, either in full (such as, of course, Call of Cthulhu (CoC) which is a game I've had tons of fun with over the years) or in watered down, borrowed state (such as Dungeons & Dragons (D&D), which borrows quite a bit more from Lovecraftiana than a lot of people probably realize.) But I don't think many gamers really want their Lovecraftiana to be scary. Frankly, I don't think even gamers of Call of Cthulhu really want it to be scary very often, and here's why.
My experiences with the CoC game fall into one of three types:
1) One-shots, which are largely played for laughs and novelty value. These also often seem to be convention games. The death and insanity quotient is often overplayed, which makes it more silly and amusing when someone crashes and burns, rather than horrible. And if it turns out to be underplayed instead, then often the GM will compensate by making things happen arbitrarily near the end, because characters dying and going insane is what's expected. These types of games can be (and often are) quite fun, but very rarely are they even attempting to be horrifying, and even less rarely do they succeed.
2) One-shots or campaigns, either one, in which it's more like a guided tour of Lovecraftiana than anything horrifying. The Disneyfication of Lovecraft, if you will. You might go on a tour of Arkham, or other Lovecraftian locations, and you hit your quota of Deep Ones, Mi-Go, Elder Things, and other eldritch horrors, but because you're expected to, and that's the point of playing the game. More serious fans of the source material literature are probably more prone to this than others, where seeing stuff that's familiar from the source material is the whole point. It becomes comforting rather than horrifying when you see a Deep One or a Mi-Go. It's more like an esoteric in-joke or wink and nudge: fan service, if you will, rather than something that's actually terrifying.
3) The long term campaign where the Gamemaster and the players, mostly, come to the table with their expectations of horror, and willingness to play along engaged. Ironically, these kinds of game rarely have a high death or insanity count. They tend to become more about creeping, lurking dread with infrequent spikes of more immediate terror. And this is where Cthulhu can be its most fun.
Now, in part because of #2, I know of some folks who say that #3 can't really be accomplished with the established Cthulhu Mythos. You really need to come up with your own take on it. Your own creatures, your own horrible books, people, and places.
Which is, arguably, the whole point anyway. If you read a fair bit of Mythos stuff, you'll see that a lot of oblique references to entities and texts, which creates the fiction of a coherent and consistant background Mythos behind all of this... but that's largely a fiction created for just that reason. What you'll also notice is that most stories' "star" monster is a unique creature, and only rarely do they make frequent reappearances. Lovecraft himself was enthusiastic about the ability of his "Yog-Sothothery", as he termed it, to sweep up the ideas of various writers in his little circle who worked in the same ouvre and aesthetic, and have a place for them. It's not only entirely within the original spirit of the Lovecraftian aesthetic to create your own horrors, it actually probably should be expected.
Plus, because you're treading unfamiliar ground (although hopefully in a familiar, almost deja vu like sense) you actually have the capacity to bring some notion of horror to your game as you do so. Some more modern writers, who were clearly heavily influenced by Lovecraft and his aesthetic have done just that. China Mieville's slake-moths, from Perdido Street Station are very effective as a Lovecraftian horror, for example.
In any case, I'm going to think about some possible creatures of my own that I could use or create (or borrow or adapt, if need be) that I think would fit into a Lovecraftian ouvre, but which aren't, on the face of it, overtly Lovecraftian themselves. And when I do, I'll post 'em here.