CODEWITHSUNDEEP
asp.netsql-serverasp.net-mvcvb.net
Pravin Pingale
Pravin Pingale

Reputation: 21

how to pass current logged in user username to SQL Trigger with forms Authentication

i am new to MVC framework and trying to build an web app with VB. I am using Active directory for authentication with forms authentication & sql server for Managing the roles. I want to create a trigger by which if any web logged in user insert, delete or update the exsting data should be copied to another table table with the username who had done so.

I am using sqlexpress 2008 R2 with iis 7.5, My apppool is configure for integrated managed pipeline mode.

require any help on how to pass the User.Identity.Name from wbsite to sql, it is always passing the system user NT AUTHORITY\SYSTEM in system_user in sql.

Upvotes: 2

Views: 2361

Answers (2)

Nick.Mc
Nick.Mc

Reputation: 19194

Here are your options:

  1. Continue connecting to the database as the IIS application pool user (SYSTEM), as you are doing now but make your web app explictily pass the windows user (as ascertained by your web app) into a field in the table when you are doing data modifications, and include that in your trigger.

  2. I think in ISS you use impersonation to pass your windows user all the way down to the database. Then you can use the database function SUSER_SNAME() But you need to give the windows user direct access to the database.

Option 1 is generally the best because you really want to avoid giving people direct database access.

Upvotes: 0

Pleun
Pleun

Reputation: 8920

I think that might be quite tedious. Due to the disconnected nature of a Web application, usually a form of connection pooling is in place.

This means that the user does not login to the database, but the application itsself. As you have discovered in your case the NT AUTHORITY\SYSTEM. These connections are pooled among different application users (that are authenticated using forms).

By far the easiest approach in my opinion is to add the username of the last update to the table itsself and use that in the trigger. Otherwise you loose all the benefits of connection pooling and you need to create a user in the database for all form users... Imagine adding a new user and the amount of administration that goes with it.

Upvotes: 1

Related Questions