I think magic is a great story device, if used properly. I think it needs to have rules and limitations (which are best if the author decides them clearly in his own mind before using magic). If there are no limits, it risks becoming a story crutch that can be used as a quick fix when the author writes himself into a corner (a once favourite story of mine has become less so since the major characters have become so insanely powerful that there's no dramatic tension - you know ahead of time they can kick everybody's ass).
When I look at some of my favourite mainstream authors who use magic in their stories (I used to be a very heavy sci-fi/fantasy reader although that has been tempered into a wider selection of reading material now-a-days), I notice some trends I like. If you've ever read any of Lawrence Watt-Evans Legends of Esthar books he uses one of the widest variety of magics I've seen, and I think he handles it quite well. All of the magics he uses (6 different kinds I believe) are very well defined. They all have their strengths and weaknesses and rules. I think this is imporant since it prevents the author from using a quick "solve" button, but allows the characters traits that make them interesting and can give them a necessary advantage from time to time.
Another author that I believe effectively uses magic is Robin Hobb. The way she uses it is to keep it limited in scope (and even make the users of some forms persecuted), and to keep it mysterious. In her novels, even those that possess the magic do not fully understand it (due to lost knowledge, but nonetheless a limitation). This keeps magic as an element of the plot and character development rather than have it completely dominate the story.
J.K. Rowling has a different approach and I also like her novels. Usually you find a world where there a special few with magic and the story concerns them and their interactions with the wider normal world. In Rowling's books, because of the point of view from which the story is told, it's more of a case of all of the charcters having magic with very few non-magic folk involved (and then only periferally). This keeps a level playing field. She keeps the few non-magical characters from being put at too much of a disadvantage by making laws around magic use and muggles.
With either approach, I think it imporant to keep things realtively even. I have no problem with some characters having more magical abilities than others (like any human ability, some will naturally be better at it than others), but it should not carry any major character above the other major characters (or even the main supporting characters). There are many other authors I've enjoyed who use magic, but I won't get into the nuances of each one. The above are just three that I thought I would mention.
B_O_B