August 24, 2004 at 5:32 am
We have an ASP web application with a VB middle tier that executes stored procedures on our SQL Server database. ASP pages call VB Dll's which execute the stored procedures. The ASP pages and VB Dll's reside on a web server and the database on a separate database server.
Currently, we are using SQL Server logins for the VB Dll's to access the database server. Each user of the web application, we only have about 25 users, has their own SQL Server login that matches the name of their application's login. With each user having their own SQL Server login, it is easy to track user activity by user with SQL Profiler.
Unfortunately, it was decided by others that we will soon begin having the VB Dll's call the database using a single windows authentication login. This causes the NTUserName and LoginName in SQL Profiler to be identical for each user running the application. The HostName is identical too b/c it displays as the Web Server.
Does anyone know how we can track changes by user, while using the new login system? An original thought was to use an application role instead, however, our developers were discovering some kind of transactions issue with using an application role. I appreciate your assistance.
August 24, 2004 at 6:41 am
Hello,
This may require some work but since the users login with their unique id, all you have to do is to pass their logon id to the COM object then to the SP. You can then write a Logging COM object or SP to keep track of the changes.
Bob
August 24, 2004 at 6:47 am
I realize that I slightly misstated my question. How may we track user activity as it is occurring to provide support for a user that may be encountering a problem with the system, once we implement the new login procedures?
August 24, 2004 at 3:28 pm
By my understanding if you wish to track comprehensive actions taken by the user within SQL Profiler then you simply need seperate logins. If you follow Thorek's route and add username as a parameter to your stored procedures you can then scan SQL Profiler results for SQL statements containing the desired user's name (within TextData), but you may not be able to track everything you would like to track that way. Actually going to the trouble of creating a logging COM object or SP sounds like overkill in your case, but would create a more permanent trace log if that is desired (regularly scheduled maintenance would be important to control growth and fragmentation). If anyone else has a more ideal solution I'd be interested since kferrier's target environment is our current environment with hundreds of users and far too many SPs to bother adding username to. So far we haven't had a real need to watch a given user.
August 25, 2004 at 5:49 am
As I layed in bed this morning not wanting to get up I came up with the following. Note that this involves maintaining individual connections for each user for the life of an IIS session or similar control which I understand to be bad coding practice. I believe scalability was the main problem with this practice, but there could be some transaction related or other concerns as well (I forget all the reasons for the belief).
Anyhow, if you establish a SQL connection when a session is created and store it asside in a manual connection pool and associate it with the user login and session Id, you may then access the individual connection from your COM objects instead of creating temporary and anonymous connections. You could then trace the connection ID from SQL Profiler. Note that this requires passing session ID into your COM objects and creating a utility to report all existing connections. You would also need to have connection closure in the session tear down process (beware stranded connections). To account for a broken connection due to timeout or some network failure your COM objects would need to have a fallback routine that would re-establish the connection should it be closed or broken (not sure how you would detect the latter).
You could further extend the above by creating a second utility that activated the sustained connection method above for individual users. In that way, only if you are trying to monitor a given user would it create a static connection on session start. The COM connection obtainer would then need to look for a static connection and if there was no such connection provide a brand new, temporary, one.
I'm not saying I personally like this approach or the one in my previous post for our shop, but your shop may find a business case for it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply