Yes, many times a dedicated attacker who has read access to your database will also have read access to your source code or config files, but many times they won't. And if they don't, then they won't be able to crack a single one of your passwords, while even with a modern and proper hashing algorithm they still may be able to crack passwords.
Take the scenario of a relatively intelligent hacking or hacktivist group, of which there've been several in the past 5 years. Let's say they're targeting someone they dislike for whatever reason, and find out that person is registered on some forum and decide to compromise the forum. (This tactic of lifting a whole haystack to find a single needle is very common for motivated attackers.) They don't care about any of the other users, they just want to try and crack the hash of one single member and have a full GPU cluster with which to do it. They're also willing to spend weeks trying to crack that one hash.
If the user's password isn't particularly strong, it's going to fall no matter what algorithm they used.
But if the forum is peppering all of their hashes, and those same attackers can only manage to gain access to the forum's database and not its local filesystem, then their chance of cracking that password goes to 0.
This scenario is a bit contrived because odds are motivated and intelligent attackers like these will end up gaining access to the filesystem and reading the pepper with enough time and effort, but the pepper is still an additional defense and means SQL injection alone won't be enough to crack passwords.