When are procedural queries absolutely necessary? I know that we tend to avoid cursors and loops within SQL Server at every cost, but what are some of the situations where you absolutely need procedural queries, and set-based queries just will not give you the results?

I understand the difference between the two, I just have never come to a situation where I need to use a cursor.  I'm wondering if there are such situations.
In my experiences, I've run into a few times when procedural/iterative approaches were warranted.

API only allows for single-row operation

If I wanted to programmatically change the data type from real to decimal in a table that has 500 mis-typed columns like this SO question asks, a cursor is a fine approach as the DDL does not allow for altering multiple columns in a single statement.

Set based doesn't scale

If you have the SQL Server MVP Deep Dives book, chapter 4 "Set-based iteration: the third alternative" by Hugo Kornelis has some great use cases for combined cursor/set based operations. Two of classic problems that the chapter author references is the Running Totals and Bin Packing.

I used the set-based iteration approach with good success for a poorly designed process I inherited at the last job. In short, there was a process that once a year had to update 50-75M rows and attempting to do so in a single set would blow our logs. By chunking the updates into smaller batches of N rows, it allowed the log to keep up and actually finished faster than the previous year when they just allocated a metric ton more disk space.