Asp-Sql Update Trigger and System_User

Trigger works great, except for one problem.

ALTERtrigger [dbo].[Loans_UpdateTrigger]on [dbo].[List]forUpdate

asinsertinto Loan_Audit

select*,Getdate(),system_user,'Update'

from Deleted

The system_user returns "DomainName\WEB$ instead of the actual users name.

Any way to get the actual users name?

thanks

Chuck Snyder

First you need to understand how the login works for SQL Server and ASP.NET.

When you log onto SQL server there are 2 types of authentication modes, windows and SQL. Windows is via integration authentication and SQL is via custom user name and password.

When a user logs onto your ASP.NET application, IIS runs the request under the context of a given user account, depending on your IIS directory settings and/or impersonation settings in your web.config. So when your asp.net application connects to the database, it to is connecting under this account if you are using integrated authentication, or if you are specifying a user id and password in your connection string that this is the account you will log into SQL Server as.

So when you use the system value system_user you are never getting the user name of your web user, rather you are getting the user name of the account that is logged into SQL, and in your case it is a domain account.

I hope that clears it up for you.