Permissioning problem in SQL 2005...need help please!

I'm doing some data-driven scripting and I have a generic test case database with logins, etc. in it. I'm having trouble with the SQL permissioning to the database. I'm referencing a system DSN in TestPartner. As long as I have the DSN set up to use my domain account (integrated security), I can read and write to the database all day. My domain account has the sysadmin role, but no explicit database permissions. I set up a user on the SQL Server called tpuser, and gave him the same sysadmin role. However, that user cannot read or write to the same database.

I then removed the sysadmin role and gave tpuser explicit permissions to the database in the 'user mapping' section (the preferred method). I set the default schema as dbo and selected db_owner and public under 'database role membership'. The user still can't read or write to the database. What am I doing wrong?

When you connect using the tpuser, execute the following command from your application:

select suser_sname()

If you are not getting back tpuser, then you are not logged in with those credentials. If you created a standard SQL Server login, assigned it to the sysadmin role, and then logged in with that account, it will have the authority to do anything within SQL Server.