Yes, it’s has both connotations - offensive and derogatory. I can’t think of any contexts where it might be said without the inherent demeaning implications, or for any otherwise legitimate purpose.

It is most commonly used by White racists in “border towns” or in areas that are in close proximity to reservations, when they are verbally haranguing Natives in public spaces or disparaging them more broadly (in private speech, say). It often precipitates bar or street fights in places like Rapid City, Billings, etc. Or you can hear these taunts at sporting events, if there is a cluster or Natives in the spectator seats, near a larger group or low class Whites. Or, if a reservation-based team is playing some neighboring school that is White-majority. These scenarios often bring out the racism that is always brewing in these populations.