Nicely written Brian and shares many of my same views. I've tended to handle inappropriate access (like not using the applicaiton) administratively. A chewing out, a writeup, or a termination if people see the need to use Access or some other tool to change data.
However, I'm curious about a couple things. What if you used a "shared login" for all users that had minimal right before invoking the app role? Would that eliminate one of the cons?
Also, for pooling issues, an app could potentially just hold a connection open. For quite a few client/server apps, this might make sense. Alternatively, you could set a semaphore of some sort in the app when the role is invoked and not reissue "sp_setapprole" if the semaphore is set. A simple check could determine "which role" you had.
Lastly, not sure the server roles item applies for this. The apps where we want to prevent access wouldn't usually have server roles for someone. There are exceptions, but not sure they're enough to mark this as a "con", more as an FYI.
Again, great article.
Steve Jones