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
