How can I set up SQL Server 2008 users without Windows/Domain accounts?
The first part is to create a login for the new user, if you can't login to the server you can't perform queries on a database.

In MSSMS Expand the Security tab, right click on Login and select New User. Click the SQL Server authentication radio button and fill in the password for the new user. You will probably want to uncheck the box for "Enforce password expiration" and "User must change password at next login" if you are using the login from an application.

Click the User Mapping page in the left panel and map the new login to a database and give them the desired roles, click OK and you're done.

With all of that said there are two things you should be aware of.


Questions like this are better asked on https://serverfault.com/ or apparently http://dba.stackexchange.com :-)
It is still better to create windows / domain accounts and either run your application pool under that context or use impersonation.