asked
Trace shows application account, not logged in user
I'm trying to audit and track the stored procedures that the users call. When I set up the trace, it shows a blank NTUserName field and the LoginName is the account name of the .NET app. How do I get the trace to show the activities of the logged in (Windows authenticated) user?
If the application is making the calls to SQL Server with an application account, then that account is the only thing SQL Server sees and so the only thing it can record. The only way to get the user login information is to make sure the application is forwarding that account credential instead of using its own account. Unfortunately, that may involve recongifuring the app in question and making sure that all users of the application have the necessary permissions on SQL Server under their own log in. Depending on what your exact goal is, you may be better off configuring the application to separately log what user is doing what.
Thanks Timothy. My goal is to record what any one user selects, updates, or deletes on the data in case we need to perform an audit. From further research while waiting for your answer, I've looked into impersonation/delegation, but Microsoft cites security concerns in that approach. SQL authentication isn't an option for the same reason. I guess the only option left is use the app to log each stored procedure that gets called as the user navigates through the app. I wish there was an easier way since it would mean that each app that we have needs to be custom built to log access.
