More often than not, it is used in a derogatory manner. Can also be a term of endearment. Personally I mostly say it when someone tells me they did something I have no interest in.
"This is my first definition on Urban Dictionary!"
"Sick ass fool...."
"Dood I just got a keg!"
"SICK ASS FOOL!"