The other day I was on a forum that had a tiny pen cursor that left a trail of ink on the screen wherever you moved it and the trail didn't immediately dissipate either leaving it on the screen.. kind of annoying. The forum wasn't related to graphics or anything either.
Nope, they can get annoying, depending on what effect they have or even what they look like. Just a normal cursor will do the job. Don't need anything fancy.
If I owned a site, I would not change the default cursor. It's very annoying, and if people want a custom cursor, they can change their actual cursor. Nobody wants things forced upon them.