Caller identity

I am using SQL Server 2000 and SQL authentication in a web
appliacation. All data access is done via single SQL Server account.
In my front end I am using forms authentication. Is there a way to
retrieve the forms identity (or just a username) from SQL Server?

Thanks,
LacSQL can only know what's passed through the connection string, which
will be the SQL username.

Of course, there might be a solution if you describe more of the
problem; are you trying to log user activity? Do you just need to know
when a certain user is logged on or off?

Stu|||[posted and mailed, please reply in news]

(lac@.myrealbox.com) writes:
> I am using SQL Server 2000 and SQL authentication in a web
> appliacation. All data access is done via single SQL Server account.
> In my front end I am using forms authentication. Is there a way to
> retrieve the forms identity (or just a username) from SQL Server?

Yes, but the form has to cooperate, and do this:

DECLARE @.bin varbinary(128)
SELECT @.bin = convert(varbinary(128), 'Username')
SET CONTEXT_INFO @.bin

Then you can retrieve the username in SQL Server this way:

DECLARE @.username varchar(128)
SELECT @.username = convert(varchar(128), context_info)
FROM master.dbo.sysprocesses
WHERE spid = @.@.spid

Note that on SQL 2005, you should replace the access to sysprocesses to
use the built-in function context_info(). (Which unfortunately is not
available in SQL 2000.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp