Change Login-Info for Activity-Monitor and sp_who

Hi

we have one 'application'-user in sysusers that makes the connect to SqlServer for all users, for example:

Application Login-User: Thomas

DB-Connect-User: AppUser

With this solution, in Activity-Monitor or with sp_who I don't know, what is the real name of the connected user.

Any possibility to change the login-information after the connect, so that i can see 'Thomas' in Activity-Monitor or with sp_who?

Thanks for your help.

Thomas

You will see only Thomas and AppUser that are used by web server to connect to server and use objects of db.

I think in your db there is a table that contain users with encrypted passwords and if you want to see who access your application when the user app try to connect with or without succes , using sp that making the verification you can put somehere in a table the information that the user is connected. Querying that table you can see who is connected.

|||

Since Login to User is 1 to M , you will not get the result as you mentioned in either SP_Who or Activity monitor. You can make a customized sp from sp_who and you can use that. Just you need to join with sys.database_principals

(a) SP_Helptext sp_Who -- Will give u the source code of sp_who

(b) Join with sys.database_principals .

the following script is taken from sp_who and then joined with sys.database_principals . sys.database_principals is database specific. So you should run this script in the particular database to get the desired result.

select spid , ecid, status

,loginame=rtrim(loginame)

,hostname ,blk=convert(char(5),blocked)

,dbname = case

when dbid = 0 then null

when dbid <> 0 then db_name(dbid)

end

,cmd

,request_id ,sdp.Name as UserName

from master.dbo.sysprocesses inner join

sys.database_principals sdp on sdp.sid=master.dbo.sysprocesses .sid

Madhu