Microsoft SQL Server forum - Caller identity

Author

Caller identity

lac@myrealbox.com

2005-06-23, 11:23 am

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,
Lac

Stu

2005-06-23, 8:23 pm

SQL 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

Erland Sommarskog

2005-06-23, 8:23 pm

[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(12
8), '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

Sponsored Links