I think this is a problem because the database code is treated differently from the application code, because so few coders understand SQL well enough to be comfortable in it.

So, as you say, the DBA's tend to be autonomous, and held to different standards. And with less ability to say "we have tech debt, we need to refactor our sprocs" because of the knock-on effect on the application code.

It's a management problem rather than a technical certainty, I think.

But if I look at a lot of the SP anti-patterns I've seen over the years, some common themes start to arise. I'd say the biggest issue is that it is more complex to maintain interfaces between two discrete systems than it is to write monolithic code paths. Seeing SP_ProcedureName accompanied by SP_ProcedureName_New is very common. It's easy to get into that situation, and tedious to get out of it.

Storing business logic in the DB also presents a lot of very tempting, but ultimately costly, shortcuts to developers. Using a non-temporary table when you should have used a temporary table, because you wanted debugging to be easier, but now you've got a concurrency issue, but it'll probably be OK, so you leave it there for 15 years. I have also never seen what I would consider to be a sensible use of a trigger. You also end up with SPs invoking SPs invoking SPs... and figuring out the side effects of calling something becomes a lot more complicated than it would be in a monolith.

I don't think they're always bad, but I do think that reasonable use cases for them are rather uncommon. When you had a lot of services directly consuming one database, SP interfaces used to make a lot more sense. But that's not a very common architecture is not very common any more. Even in large enterprises, you'll typically see new projects being developed as web applications, even if they're only served over the corporate network.