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 |
|
Jatsie
Starting Member
15 Posts |
 Posted - 2008-11-17 : 05:50:20
|
| Hi, I have coded the triggers to keep an audit trail of changes (delete, update, insert) to my employees table on sql2005 database. The previous programmer at my company developed a dotnet web application with Ironspeed. This application has a username and password screen on login. I need to intercept this username and password to keep audit trail in my sql trigger. I have tried suser_sname, host_name(), etc. All is get is sql connections username or the machine name of the application server.....Any help would be greatly appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-17 : 05:55:54
|
| thats because you have defined an application role for your application.So it always uses that service account for execution. |
 |
|
|
Jatsie
Starting Member
15 Posts |
Posted - 2008-11-17 : 06:11:15
|
| can you please suggest a sollution, I am clueless. I cannot change the application since Iron speed was not purchased by my company, but owned by the previous developer. How can I get the login names? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-17 : 06:31:39
|
The "ironspeed" application either calls a stored procedure to check if supplied username and password is a valid combination, or the "ironspeed" application select from a user table directly.Use SQL Profiler to see which case is valid. You can filter SQL Profiler to the certain machine from where the application is run.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Jatsie
Starting Member
15 Posts |
Posted - 2008-11-17 : 07:27:13
|
| Profiler yields:exec pFermarTbl_systemusersGetList @p_join_str=NULL,@p_where_str=N'((UPPER([Tbl_systemusers_].[Userlogin]) = N''ARMAND'') AND (UPPER([Tbl_systemusers_].[Userpassword]) = N''DANIE@''))',@p_sort_str=N'[Tbl_systemusers_].[RecordId] Asc',@p_page_number=1,@p_batch_size=1This shows me that a stored procedure is doing username and password validation. Would you suggest that I modify this stored procedure to store the username? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-17 : 07:36:47
|
Yes.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Jatsie
Starting Member
15 Posts |
Posted - 2008-12-04 : 02:51:52
|
| This is a good start, but knowing who is loggin in is not enough, I need to connect this with deleting of records etc. |
|
|
|
|
|
|
|
|
Trigger not picking up Ironspeed login name