I'm still not sure if what you are reading into this is going to do what you originally wanted.
Let's say the IIS server is X, the SQL Server is S, and the user is JOE.
If you create a SQL Server account for IIS to use, let's call that STUFF.
If JOE logs in, connects to IIS, and makes a change in the database via the connection, and you check who did it, you see it is STUFF. JOE, TOM, MARY, MIKE... all are going to show up as STUFF if IIS uses the same connection string.
There are three approaches to dealing with this that I can think of, if what you want is an audit trigger to know who made a change.
1) Simplest to code: Change the connection string to use integrated connections, make the servers trust each other (if W2K/SQL2000 or higher only, see "delegation"), and be sure IIS disallows anonymous connections. Then magic happens and the real user JOE is visible from SQL. HOWEVER, this means you must give JOE write access to the tables. If JOE Should not have that EXCEPT though your IIS apps, this does not work. Server X must be marked "trusted for delegation", and there are some other issues your network admin might or might not cooperate with.
2) If you control all the code, then set up some semi-permanent setting associated with the connection to record who it is, and have the trigger check that. We do that by calling a stored procedure in master as part of each write-capable connection when the write-capable connection is under a generic ID. The procedure saves the real user name (which IIS knows) in a table associated with the connection information, and triggers look there.
3) Create some kind of association table, so AD id JOE gets a SQL id JOE, etc.; do not give those passwords to the users, store them somewhere safe. Have IIS notice the AD id JOE, look up the SQL server id JOE, and use that in the connection string. A pain to administer, and a pain to keep this table of ID's invisible from everyone.
Note that (1) is easier if IIS and SQL are on the same machine, as no delegation is involved so the network setup is simpler, but the risk of giving direct write acess to the users may not be acceptable.
We use a combination of (1) and (2). We give write access only where it is appropriate inside and outside the application, and do trusted delegation and use real user names. This way we also know who is running read-only queries, which is very useful. For write access where JOE should only write when going through the application, we map to a generic account but pass over the ID separately as in (2) so audit triggers can record it. Because we use a reusable .net object for the connection, we only had to build that code once and it gets executed in each connection context.