All Unreal Tournament games have delicious sniping. It's fast, feels powerful, and comes with an announcement if you're good enough to head shot someone. I miss the days when multiplayer was only about UT or Quake 3 (or CS, I guess).
FarCry 2 allowed for some great sniping. Sure there was no bullet drop or stuff like that, but there's a lot of fun to be had taking out a whole enemy base without them knowing where you are.
And if you want to get really old, Delta Force had some great sniping with rudimentary bullet drop. nothing quite like shooting a several millimetre tall stick-man from across the map on a resolution of 640x480 (I think)