So I've always been a fan of more interesting magic items, especially those with powers a little more difficult to classify or put into mechanics terms. I remember sitting in art class in grade school and trying to come up with the strangest combination of powers/defects for the various artifacts listed in the 1e DMG. IIRC the artifacts were potent but you also had a set of coded additional effects and things which the GM could and would assign. Not that I ever saw any of these things in any of the games, but we read and talked about the stuff in the books more than we ever actually played at the table.
So I'm less fond of items that can be quantified or have a simple mechanical premise. I'm sure that holds true for most gamers. Sure the +2 sword is useful, but everyone would like something with a little more color. That's why I always appreciated the insanity of Creatures & Treasures for Rolemaster, which had your basic utility items, but also had some stuff with really odd powers. It was a far cry from GURPS Magic Items which tried to make sure everything could be clearly built and done within the limits of the rules presented. I recall picking up a d20 magic items supplement-- hardback no less dedicated to either swords or staves, I can't recall. You'd get like a two-page badly written fiction piece and then a block which went something like “Wand: Fireballs”. Not quite that bad, but pretty close. In any case, I thought I'd mention some of my favorite items from the campaigns. Enough time has passed I'm not sure, with some exceptions, how much are things I came up with and how much came from source materials. Where I can remember, I'll note that, otherwise assume I'm simply relaying information and not claiming credit for the idea.
Cloak of Eagles: From the Freakish Band of Adventurers campaign. Essentially it was a cloak that held a flock of eagle within it. When swirled and activated, the eagles would stream forth, being usable as a distraction or an attack. At the end of the summoning, the eagle would fly en masse back into the cloak. Interestingly, the character who ended up with this item, Sir Tobias Crank, rarely used it. He was worried about the poor eagles and never wanted to throw them out into the middle of a fracas. And yes, the item was a reference to the song by T-Rex.
Crow Gun: From our Unknown Armies-inspired City of Ocean campaign. I think they found this in a bad guys safe but weren't sure exactly what it was. Barry IIRC fired it when they got found out. The gun lets forth a steam of crows who swarm over the target and pick the body clean before returning to the gun. I think Barry ended up throwing up after he actually used it. It certainly horrified the rest of the group-- while there was high-strangeness in that game, I think this was the first object the had ahold of that created such a blatant and terrifying magical effect.
City on a Pin: I think at least the inspiration for this came from something, but I ended up taking it in a very different direction. Essentially the group found a pin which gave off significant magical energy, acting as a mana stone. They weren't really sure what the deal was with it. Eventually they found by connecting with the stone they could enter into the diamond atop it, in which they found a castle and part of a city, empty and abandoned.
This would eventually lead them back to a mage named Nudor, a character who was a former PC who had finished a previous campaign with the sole and abiding intent of finding a way to create no mana magic. Nudor was, in a word, utilitarian. He'd conducted a number of experiments-- including some what had awful consequences. The pin turned out to be the result of one of those-- Nudor having compressed and destroyed the city “by accident”. The pin essentially drew its energy from the unraveling of the material trapped within it...beginning of course with the people who had lived there. While the object itself was a pretty basic magic item (mana stone which works in no mana zones) the backstory for it nicely dovetailed into some of the themes and plots of the campaigns
Pudding Sword: A magic sword with the ability to set the taste of any food it touched. Sounds silly, but it did have a variety of applications-- military and more sinister. The sword was dubbed the Puddling Blade once people realized it could define both taste and texture.
The Elemental Spheres: These originally came from the Mayfair supplement Arch Magic. Essentially they were glass spheres which contained an immense amount of whatever element they were associated with. The sigils etched on the outside of the sphere could be written out over the landscape, then when the sphere shattered, the element would release and follow that course. They could be used to raise mountains or create mighty rivers. The Sphere of Fire happened to be in the treasure vault of a flying city when the group crashed it. That caused the sphere to wildly explode, destroying the flying city and another nearby one. Not the result originally intended.
Elemental Forges: A continuing conceit in my campaigns is the existence of Elemental Forges-- forges which can be used to temper metals with the raw power of an element, potentially imbuing it with effects and affinities. Of course using such a forge required special equipment and training, but they were always a sign of a particularly potent magic. I borrowed some of the elemental conceptions from Rolemaster's Elemental Companion (with a bunch of new and interesting elements) which led to some cool ideas, such as the elemental forges of Gravity and Void.
The Nemesis Chain: I'm pretty sure this came from Creatures and Treasures. The premise was that when placed around someone's neck, it would create and exact duplicate of that person with an undying hatred and passion to kill them. In one of the fantasy GURPS campaigns they'd gotten this item early on for some reason and I'd quite frankly forgotten about it. They found themselves trapped in an underground complex against an adversary called the Thanatologist, an executioner for really powerful lords of the elements. Over the course of several rounds, this character took apart an already injured party. Then one of the players leapt onto him and dropped the necklace around his neck. In the ensuing chaos, the group beat a hasty escape and sealed the original and the duplicate in the tomb. In a later campaign, set some thirty years after that, another party entered the same complex, only to find the two immortals still fighting one another.
The Coins of Belsham: Another one of those cases where one of the PCs got some magic items early on in the campaign and hoarded them for later use. In this case, they were a set of legendary coins, each with a relatively potent one-use power. This was in fantasy GURPs, so the group was relatively low powered by the standards of your conventional fantasy campaigns (i.e. they could get taken out by a good sword stroke or lucky hit). Three of the players found a fog filled room in a dungeon-like complex. They saw a shape looming forwards towards them.
Which turned out to be a Beholder.
Now, you have to understand that this was late in the campaign, but they really hadn't fought any classic “monsters” per se-- mostly wolves, bad guys, and the stray Orc. So they obviously freaked. One of the players critically fumbled a fright check and ran screaming away (or would have it he wasn't a mute). The second player, a barbarian played by Art Lyon, dashed forward and jumped on the beast. He tried to grapple with the eye stalks. The beholder hit him with a petrification beam and then followed up with a blast of energy which reduced him to tiny bits of rubble.
Meanwhile the third player pulled out and tossed one of the coins-- which I'd forgotten about. Timestop. His character had a full minute of free action. He walked up to the beholder and drove his sword into it about thirty+ times and then stepped back. Time resumed and the beholder fell to the floor a wet and squishy mess. A good use of a limited resource and interestingly the only one of the five coins he used during that entire campaign. The rest, with different powers, retired with his character.