The term was originally used as a way to separate Latter-Day Saints from other Christians, due to their belief in the Book of Mormon as well as the Bible. (Much like the term "Christian", which was first used by Jews in the early Apostolic period to distinguish this sect of Judaism, who believed that Christ had already come, from the more respectable, mainstream Jews.)
(EDIT: Because this apparently confused someone, I'm using the word "Christ" in the original, literal meaning here. Christos is the literal Greek translation of the Hebrew messiah, and was used as a title rather than a name at that time.)
In the early days of the church, the term commonly used by LDS members to refer to each other was "saints". (This can be seen throughout the Doctrine & Covenants, a collection of modern-day revelations regarding the church received by Joseph Smith and his successors, and in surviving letters from the period.) But again like the term "Christian," Mormons ended up adopting the term once it became ubiquitous.
The church currently sponsors the well-known Mormon Tabernacle Choir, and runs an official outreach site at www.mormon.org, so it's safe to say that it's not considered offensive, although the official name of the church does not contain the word and it's considered proper to use the full name, or simply "Latter-Day Saints," in any sort of formal context.