I'll give my take for what it's worth....
I've tried testing this out at all different sampling rates over the years. I've done the blind tests, I've tried great interfaces, cheap interfaces, line recording, mic recording....I hear differences but I can't tell you what they are. The differences that I can sort of explain:
Cheap interfaces: They sound better at higher sampling rates but don't get too excited because they aren't sounding better at all when compared to something good. At higher rates they sound as good as a really good interface at 44.1 in MY experience.
Good interfaces sound the same to me at all sample rates. However, I HAVE (like Craig) noticed differences with guitar sims at higher rates too. BUT, to my ears it's not enough of a difference for me to go that high. As a matter of fact, I prefer the warmer, grittier sound of a Realtek stock card for guitar sims. Why you may ask? They aren't as clean yet they aren't dirty in a bad way. The filters make dirty rock sounds and lightly dirty sounds more pleasing to my ears. I know...that sounds ridiculous. But it's funny, all the guitar tunes I've done on my little internet machines running Realteks via ASIO I always know without thinking. I can't even explain what the heck it is...I just prefer some of the sounds I've created using guitar sims as well as some of my guitar pre-amps through those cheesy stock cards.
When I have noticed fairly noticeable differences in higher rates, it has been in a mic'd situation. Example...
I recorded an orchestra one time at 24/48. They tried a song they weren't quite ready to do and screwed up after about a minute. They wanted to try it one more time. Just for my head, I saved the other project, opened up a new one and set it for 24/96. When I got the projects home and worked on mixing them, I noticed the higher sample rate just sounded different. I can't even tell you it was better....it was just "different." The only way I can explain it would be "more brilliant sounding". Maybe a cleaner high end? I don't even know.
So in my opinion, I think it matters what you record as well. Meaning, a hard rock, metal band or techno artist is not going to benefit from 24/96 (unless a synth used might) but I sincerely believe an orchestra just might. The reason for my theory? The more sonic something is, the more I feel it will not benefit from higher sample rates. I have no idea if I'm right in my belief and to be honest, I haven't given it much thought. The reason for this?
Like John T, my main concern is getting all my sounds to be right before I print anything. Digital is going to give you exactly what you put into it.....even at 16/44. Good sound in, good sound comes out regardless of bit/sample rates. I feel a good interface is important...but sample rates....I'll never go higher than 48 on my end. No particular reason other than the majority of my clients use 24/48....the people that taught me used it and it's just become a part of the way I work. :)
-Danny