Nice article, Brian!
I share your opinion about application roles and would like to give you one workaround how users can not access the database outside the application without using application roles.
You say: "If I grant INSERT and UPDATE rights to the users via a normal database role, that means these particular users could make changes in TroubleTickets through Microsoft Access, Query Analyzer, Enterprise Manager, or some other tool. "
To work around this problem, one of the major CRMs uses this: they change the user password on the way to the database. The user uses his SQL Server standard login to login the the application and the application submits the modified password to the database. The passwords get created and reset through the application, so the user never even knows his real database password.
One catch here: when the password gets created or modified the real database password could be traced with the Profiler. To run SQL Server Profiler, users must be granted the ALTER TRACE permission per BOL. They can also get the changed passwords from the trace file if the traces are set up and the user has access to the folder where the trace files are stored and has knowledge how to read them etc. But: if the user is that smart maybe he has to be the admin on this server for the first place. 🙂