First, I tend to encapsulate permissions in database roles rather than attach them to single user principals. The big win here is roles are part of your database, so you can completely script security then tell the deployment types to “add a user and add him to this role” and they aren’t fighting SQL permission boogeymen. Furthermore, this keeps things clean enough that you can avoid developing in db_owner mode and feel alot better about yourself–as well as practice like you play and generally avoid any issues.
Insofar as applying permissions for that role, I tend to cast the net wider these days, especially if one is using ORMs and handling security through the application. In T-SQL terms, it looks like this:
GRANT SELECT, UPDATE, INSERT, DELETE, EXECUTE on SCHEMA::DBO to [My DB Role]
This might seem a bit scary at first, but it really isn’t — that role can’t do anything other than manipulate data. No access to extended procs or system procs or granting user access, etc. The other big advantage is that changing the schema–like adding a table or a procedure–requires no further security work so long as you remain within that schema.
Another thing to take into consideration for SQL 2005+ is to use database schemas to secure groups of objects. Now, the big trick here is that many ORMs and migration tools don’t like them, but if you render the default schema [dbo] to the app, you can use alternative schemas for special secured stuff. Eg–create an ADMIN schema for special, brutal database cleanup procedures that should be manually run by admins. Or even a separate schema for a special, highly secured part of the application that needs more granular DB permissions.
Insofar as wiring in users where you have separate boxes, even without a domain you can use Windows authentication (in Sql Server terms integrated authentication). Just make a user with the same credentials (user/pass combo) on both boxes. Setup an app domain to run as that user on the web box and setup a Sql Server user backed by that principal on the sql box and profit. That said, using the database roles can pretty much divorce you from this decision as the deployment types should be able to handle creating sql users and modifying connection strings as required.