The only 3 places that I can think of using it are for ...

1. Batch control like Jim has been talking about. Still, I prefer other methods than a cursor for this and only because I went so long without ever even thinking of using a cursor. This could include "stepping" through a list of filenames to import, doing "mega deletes" a couple thousand rows at a time, or running a stored procedure or bit of built in code that was (poorly) designed to do just one thing at a time (sp_send_dbmail is a good example).

2. Another use is to run some form of meta-data change or check on multiple tables/things across databases. For example, you might write one to find a particular column name in all tables of all databases and tell you where it's been used.

3. Last but not least, there are the rarities when only RBAR will do such as the ol' bin-stacking problem.

For all the items, a well written cursor can easily tie and sometimes beat what someone might do with a Temp Table and While Loop. For 1 and 2, neither will be the source of a performance problem.

For those getting ready to suggest recursive CTEs, those can actually be as bad or worse than a cursor or While Loop depending on what they're doing. In general, anything that counts (increments) one value at a time is going to be a real resource hog as an rCTE.

The biggest problem with cursors, While Loops, and rCTEs that I've seen is that people do have a hard time figuring things out in a set based fashion and give up to quickly and resort to one of these forms of looping. There is a paradigm shift required that can most easily be stated as I have in my signature line below.

p.s. And, yes... I agree that 1 and 2 can be done without a cursor or loop but the cursor or loop will not be the performance problem for either.