Best practice on users/roles on SQL Server for a web application

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.

For a long time the SQL Server guidelines for application access to the database were to isolate access to data into stored procedures, group procedures into a schema and grant execute on the schema to the principal used by the application. Ownership chaining would guarantee data access to the procedure callers. The access can be reviewed by inspecting the stored procedures. This is a simple model, easy to understand, design, deploy and manage. Use of stored procedure can leverage code signing, the most granular and powerfull access control method, and the only one that is tamper evident (signature is lost if procedure is altered).

The problem is that every bit of technology comming out from the Visual Studio designers flies in the face of this recommendation. Developers are presented with models that are just hard to use exclusively with stored procedures. Developers love to design their class models first and generate the table structure from the logical model. The procedure based guidelines reuire the procedures to exists first, before the first line of the application is written, and this is actually problematic in development due to the iterative way of modern development. This is not unsolvable, as long as the team leadership is aware of the issue and addresses it (ie. have the procedures ready, even as mocks, when the dev cycle starts).

Create a user 'webuser' that the web application uses.

Only grant stored proc execute permissions to this user. Do not allow direct table read/write. If you need to read something from a table, write a proc. If you need to write data, write another proc.

This way everything is kept nice and simple. One app user, with only the relevant permissions. If security is compromised, then all the intruder can do is run the procs.