from a trigger, how to find out who modified data on table X while that user is logged from a generic dbuser but got the right from a user-table (id, name, password)

Question

I'm not sure how to formulate that question but:

1. you have a webpage

2. the webpage got a specific user/pass in the web.config for the connection string

3. on a webpage you ask for a user/pass that is connected to a table(id, name, pass)

4. the user is recognized with a valid user/pass and now you know the id from the table above

5. the user change some data in a table and that table got a trigger

from that trigger, how to retrieve the user id from step 4

edit

let say the user is logged using the asp.net membership table

Answer 1

you need to have a LastChgID column (or similar) on the table they are changing based on the web page user/password, then INSERTED.LastChgID will tell you. otherwise, you are out of luck.

EDIT When you save the change, store the web apps user ID into the table's LastChgID column, this may require passing it into the stored procedure, or just SET that column in the UPDATE statement. When the trigger fires, INSERTED.LastChgID will have the web apps user ID.

Answer 2

In step 4, when you say that YOU know it, what you really mean is that your application knows what user id is logged in. Your application's authentication is completely separate from your database authentication (excepting maybe using windows authentication with SQL server, but I don't think that's what you're doing).

As KM mentions, you would need to pass the application user id to the trigger by means of a "LastUpdatedUserID" column or some such thing on the table being updated.

Answer 3

@KM, or move your users into AD and use integrated auth. No other option here.

Answer 4

Use SET CONTEXT_INFO and CONTEXT_INFO() to pass out-of-band parameters. Your Web layer must ensure it sets this correctly on each connection it uses prior to calling into the database, which means one extra additional round-trip to the database.

Answer 5

Since the username is just data it is tough to capture via a trigger.

Option #1 is similar to what KM said and your developer would have to pass the username via a query and update an audit column in the database. and the trigger would grab that column vlue on updates and do what ever you want with it.

Option #2 would be to programatically create the user in SQL server or your windows domain structure, give it access to the application and then impersonate that user upon entry for subsequent logins. This would be an administrative maintenance issue, but the application users would then access the database using their unique ID instead of the one configured in web.config and all updates to the database are as that user instead of the generic one supplied in web.config.

Hope this helps.

Answer 6

As has already been suggested (by Remus Rusanu) using the SET CONTEXT_INFO for this means you don't have to add parameters on all your stored procs to do this. A similar question from myself can be found here:

http://stackoverflow.com/questions/323494/sql-server-modifying-the-application-name-property-for-auditing-purposes