May 14, 2009 at 4:39 am
I want to use user information for data auditing so if someone makes a change it can log their IP address, Windows Logon, Machine Name. Can SQL Server access any of these things via @@ or otherwise - and if so how?
Any help much appreciated!
May 14, 2009 at 5:13 am
Take a look at the sys.dm_exec_sessions and the sys.dm_exec_connections DMVs. You can use @@Spid to get the spid of the session to filter those DMVs.
Doing auditing in triggers?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2009 at 5:33 am
Thanks I'll check that out.
Not sure exactly where I'll use the data yet - I take it doing this from triggers is not the best way?
May 14, 2009 at 6:52 am
Well, on versions before 2008 there really weren't many options. Triggers, properly implemented, do auditing quite well.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2009 at 8:09 am
OK - that's good to know. Thanks for you comments.
I think I may not be able to capture what I need from this anyway as all my users connect via ODBC and the IP address seems to be that of the server itself not the PC they are using.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply