This is just a script that I put together to create a user and assign it database access and roles. The login actually existed previously with the wrong roles, so the script starts out by deleting the login from both the database and the server. Here are the commands I entered and the server's responses:
-- Start up OSQL from the command line
c:>osql -S SERVER_NAME -d DATABASE_NAME -E
-- Set the database to the one we want to
-- grant access to
1> use DATABASE_NAME
-- Drop the login's database access
1> sp_revokedbaccess @name_in_db='USER_NAME'
User has been dropped from current database.
-- Drop the SQL Server login
1> sp_droplogin @loginame='USER_NAME'
-- Add the SQL Server login with the
-- correct password and default database
1> sp_addlogin @loginame='USER_NAME', @passwd='PASSWORD', @defdb='DEFAULT_DATABASE'
New login created.
-- Grant database access to the login
1> sp_grantdbaccess @loginame='USER_NAME'
Granted database access to 'USER_NAME'.
-- Grant the login the "db_owner" role
-- to this database
1> sp_addrolemember @rolename='db_owner', @membername='USER_NAME'
'USER_NAME' added to role 'db_owner'.
This was for a login with "Standard" security-- that is, not a domain user name.