SQL Server "CURRENT_USER" and "user_name()"

[Jim Moon]

Hello.

We have SQL Server Stored Procedures that use the "CURRENT_USER" variable.
We have SQL Server database fields that call "user_name()" as a default.

That has been fine in the use and storage of user names, in the context of
being called from IIS 5 and 6, from ASP 3.0 using ADODB.
I'll add that the web applications are using Basic Authentication. So,
individual user account names are garnered and stored. That is valuable to
us.

As far as I can tell, there is no way to obtain the same behavior with
ASP.NET, ADO, and SQL Server. The "CURRENT_USER" variable contains ( and
"user_name()" returns ) the "ASPNET" user if the database is hit from
ASP.NET being run in IIS 5. If ASP.NET is being run in IIS 6 in "worker
process isolation mode", instead of the "ASPNET" user you get the "NT
AUTHORITY / NETWORK SERVICE" user (unless you change that by use of the
"identity" field in the web.config file).

Here's a link to the web page that best summarizes the context of my
dilemma:
http://idunno.org/dotnet/trustedConnections.aspx

Using ASP.NET, IIS 6 and SQL Server, is there any way for the actual user's
logon to show up in SQL Server's "CURRENT_USER" variable and get returned
from SQL Server's "user_name()" function?

Thanks,

Jim

[bruce barker]

use basic authenication and in web config turn impersonation on - will work
just like classic asp

-- bruce (sqlwork.com)
"Jim Moon" <jmoon()at()uab.edu> wrote in message
news:Of**************@TK2MSFTNGP11.phx.gbl...

Hello.

We have SQL Server Stored Procedures that use the "CURRENT_USER" variable.
We have SQL Server database fields that call "user_name()" as a default.

That has been fine in the use and storage of user names, in the context of
being called from IIS 5 and 6, from ASP 3.0 using ADODB.
I'll add that the web applications are using Basic Authentication. So,
individual user account names are garnered and stored. That is valuable to us.

As far as I can tell, there is no way to obtain the same behavior with
ASP.NET, ADO, and SQL Server. The "CURRENT_USER" variable contains ( and
"user_name()" returns ) the "ASPNET" user if the database is hit from
ASP.NET being run in IIS 5. If ASP.NET is being run in IIS 6 in "worker
process isolation mode", instead of the "ASPNET" user you get the "NT
AUTHORITY / NETWORK SERVICE" user (unless you change that by use of the
"identity" field in the web.config file).

Here's a link to the web page that best summarizes the context of my
dilemma:
http://idunno.org/dotnet/trustedConnections.aspx

Using ASP.NET, IIS 6 and SQL Server, is there any way for the actual user's logon to show up in SQL Server's "CURRENT_USER" variable and get returned
from SQL Server's "user_name()" function?

Thanks,

Jim

[Jim Moon]

Thank you, Bruce.

Inside
<system.web>
I added
<identity impersonate="true" />

It worked like a charm!

Jim
"bruce barker" <no***********@safeco.com> wrote in message
news:Ox*************@TK2MSFTNGP10.phx.gbl...

use basic authenication and in web config turn impersonation on - will work just like classic asp

-- bruce (sqlwork.com)
"Jim Moon" <jmoon()at()uab.edu> wrote in message
news:Of**************@TK2MSFTNGP11.phx.gbl...

Hello.

We have SQL Server Stored Procedures that use the "CURRENT_USER" variable. We have SQL Server database fields that call "user_name()" as a default.

That has been fine in the use and storage of user names, in the context of being called from IIS 5 and 6, from ASP 3.0 using ADODB.
I'll add that the web applications are using Basic Authentication. So,
individual user account names are garnered and stored. That is valuable

to

us.

As far as I can tell, there is no way to obtain the same behavior with
ASP.NET, ADO, and SQL Server. The "CURRENT_USER" variable contains ( and "user_name()" returns ) the "ASPNET" user if the database is hit from
ASP.NET being run in IIS 5. If ASP.NET is being run in IIS 6 in "worker
process isolation mode", instead of the "ASPNET" user you get the "NT
AUTHORITY / NETWORK SERVICE" user (unless you change that by use of the
"identity" field in the web.config file).

Here's a link to the web page that best summarizes the context of my
dilemma:
http://idunno.org/dotnet/trustedConnections.aspx

Using ASP.NET, IIS 6 and SQL Server, is there any way for the actual

user's

logon to show up in SQL Server's "CURRENT_USER" variable and get returned from SQL Server's "user_name()" function?

Thanks,

Jim