geno wald (4/3/2008)
This appears to be pretty much what I would want, except that I would like to be able to capture the machine name associated with the user who is making the connection. When I view the Activity Monitor in SSMS under the Management node, I can see the client computer name identified as "Host", but it is not clear how I can access that value from the trigger.The reason this would be helpful is that there might be several people who share access to an account. Using the client computer name to distinguish where the login came from would help identify the person making those ddl changes.
Within the database and/or server trigger you may create another variable to capture the hostname and any other information you would like from the sys.sysprocesses table.
e.g.
DELCARE @hostnm VARCHAR(50)
SELECT @hostnm = hostname
FROM sys.sysprocesses
WHERE loginame = suser_sname()
and cmd <> 'awaiting command'
You would then alter the tables hosting the information to add a column for host name. Finally, you may also want to capture the "net_address" column within the sys.sysprocesses table to get the MAC address of the network card as well as the hostname.