who gets to run the app is another whole deal (and none of my concern).
But, at least where we work, our DBA are having fun with schemas and permissions. I have to log in as one user and my tables are owned by yet another user. I access those tables through stored procs owned by yet another user.
So - permission to select, insert, update, delete (sigh, no truncate) granted to the stored procs.
Permission to execute granted to my login for the stored procs.