I tend to classify any difficulty where I wouldn't willingly play it again as truly hard. A lot of the time beating harder difficulties is just moving up as a matter of course, and I wouldn't dream of playing on the lower difficulties again. There are some exceptions though.
Due to bad design, Resident Evil 5's Professional difficulty is one. One hit from any enemy will knock you into the "dying" state where your partner has a set time to save you from death. That in itself is acceptable. What is not acceptable is that in Professional your partner has literally a second to save you. It is bullshit. It forces you to be constantly glued to each others' sides, and if playing alone, the AI is retarded and often fails to save you even if standing right next to you.
Mass Effect 2's Insanity difficulty goes into this category for me. A number of overpowered enemies pretty much step into broken on this difficulty (I'm looking at you, Scions...) and the game just becomes one massive experience of hiding behind cover. Making the slightest tactical mistake, bobbing out of cover at the wrong instant... these things get you killed instantly. Enemies that can attack through cover or force you out of cover with abilities are a nightmare. I feel Hardcore, one step down, is the most entertaining difficulty.
StarCraft 2 on Brutal is fucking BRUTAL! I had to resort to the internet and seeing mission guides a few times. Often missions can only really be solved in one specific manner. It becomes frustrating as a result, since the thing I enjoy about StarCraft 2's campaign is that you can play it your own way. Not the sort of experience I'll willingly repeat.
Lastly, Bayonetta on Non-Stop Infinite Climax. Aside from the standard things such as increased enemy damage, one of the most important game mechanics, Witch Time, is disabled. Witch Time temporarily slows down time if you dodge an attack at the last moment. It is often one of the best ways to defeat a lot of enemies. Fighting through without it is damn challenging. Additionally, quick times are reduced to a fraction of a second, and some mini-games you need to play through in the game become obscenely hard. Harder than the combat itself, really.