Logins and users question

Ok, let me preface my question with a little info about the application I am working on. I am in the middle of creating a project in VB.Net for my company. We have a data management system for handling sampling data. Now the database design is like so. The application is able to handle multiple "Sites". We create a new database for each site that is create dint eh application. These databases are identical in structure but the data is obviously different.

Now we don't actually delete any records in this application rather we mark items as deleted instead. This allows us to easily undo any changes that have been made to the data. When a change or delete are made, we record this change so that reports can be printed to show what changes were made and by who. This is all well and good, but my thoughts are this.

At the moment I am writing lots of VB code to handle adding these records of change and inserting them into the database... What I would like to be able to do is to just create Triggers on the tables to add them. This is something that I know how to do and i feel like it would be the better way to do it except for one thing... here comes the issue...

I have no way of knowing what user is logged in to my application from within the trigger because the application uses a single login for accessing the database. My thoughts are this... Would I be crazy to think that it would be a good idea to create SQL server logins for each user that is created in the application, giving these users access to only the database that they have been created in? This would allow me to determine who was logged in when the change was made and could then implement recording changes through triggers... I am not a very experienced dba programming is more my speciality. I know how to implement this idea, but I am just wondering if this sort of thing is considered bad practice or if it is something I should consider implementing...

Sorry for the novel there and thanks for any help or insight.

BrianNot sure if this helps but there is the suser_sname() which should return the login name (I usually put this in as the default value for my EnteredBy field and I'm not sure if it will work in your case with the single login but it may be worth investigating.)|||Well, that is what I am looking for, but I was wondering if it makes sense to create a SQL server login for each user in my application.|||Well, that is what I am looking for, but I was wondering if it makes sense to create a SQL server login for each user in my application.

It would seem so if you want to track who is inserting or modifying your data!|||We use connection pooling wih a single login to the database

The users have application security, and they log into the app via a user table, with passwrod verification done by an API to RACF

Every database call is limited to Stored Procedures

The Calling App must pass as a parameter to the sproc, the application user that is logged

Any modification that is done records the app user on the row

A trigger then moves the before image row to history as is

The last person making the modification is on the current row