Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
memento
Starting Member
16 Posts |
 Posted - 2006-06-01 : 05:50:36
|
I have a web application that will use a SQL database. I need an audit implementation in the database that logs every insert, update and delete that happens, as well as the user responsible for the change.Right now, to record what user has made the change, I haveinsertuser varchar(128) default suser_sname() This works well while working in MS SQL directly. The problem is when I access through the web interface, the username is machineName\ASPNET and it doesn't specify the exact username that has made the change. I have currently Windows Authentication in the connection string used to access the database. Does anyone know how to get the specific ASPNET user that is logged in and making the changes? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-06-01 : 06:09:26
|
if you are using stored procedure to perform the insert, update / delete, pass the user id into the stored procedure and update the column accordingly
KH |
 |
|
|
memento
Starting Member
16 Posts |
Posted - 2006-06-01 : 07:09:58
|
quote:
Originally posted by khtan
if you are using stored procedure to perform the insert, update / delete, pass the user id into the stored procedure and update the column accordingly
KH
So getting the session username and sending it explicity to SQL through a stored procedure is the only way to SQL know who's logged in? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-06-01 : 07:32:50
|
yesGo with the flow & have fun! Else fight the flow  Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
|
memento
Starting Member
16 Posts |
Posted - 2006-06-01 : 08:41:38
|
| This approach implies that the stored procedure receives all the arguments plus the login name argument and inserts the values in two different tables (the real table and the audit table).I have a trigger implemented that automatically registers Inserts, updates and deletions of the table in its corresponding audit table, regarding if the I,U,D operation was done in a stored procedure or not.My only fear with this stored procedure implementation is that the insertion of the login name is not automatic, ie, the stored procedure has to say "insert the record in the table and, after that, insert the login name you have in the audit table". If, for some reason, a person makes an I/U/D operation without using the stored procedure, the database won't register his login name.Does anyone have an idea about improving this solution, so that I can ensure that the login name is always inserted in the right audit table record? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-06-01 : 08:50:02
|
| you pass the loginName to the sproc that inserts data to original table.audit trigger then just takes the loginName from inserted pseudo table and that's it.for bulk inserts triggers don't fire anyway.any other source of data you havo to handle yourself.then you come to deletes.for deletes you have to have a before trigger to do auditing.if all your access is through sprcos then use sprocs for auditing too.Go with the flow & have fun! Else fight the flow Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
|
|
|
|
|
|
ASPNET user logged in in SQL