Trigger using sp_who ? Is it possible?

[Pebkac]

Is there a way to set up a trigger or message when a user logs in under a different ID name?

I am specifically looking for when the Host name is not in the loginname

Example:
Loginname =Domain1/User1
Host =User1
is OK

but I want to know if this occurs
Loginname =Domain1/User2
Host =User1

is this possible at all?

 

[clapag22]

Well, you cannot create triggers on system tables (in this case sysprocesses).

You may be able to do something in your app where you grab the login name (suser_sname()) and compare it to the sysprocesses (nt_name, nt_domain, hostname fields) table.

 

[Pebkac]

OK. I just dont know where the info is coming from via the SP. Is there a base table I can use conditions on?

The app only has a table with the user name.
Is there a view or table I can create that will show me the Host and username from SP_who2 that I can then put conditions on to watch?

 

[clapag22]

As far as I know, the only table where this info is available is sysprocesses. Which lives in the Master DB.

Another idea is to insert the results of sp_who into a temp table and compare the results of that temp table to your users table in your app. This way you're not directly accessing a system table.