Can SQL fetch user variables

Question

Can SQL fetch user variables

Matt Toynbee

SSC Enthusiast

Points: 174
More actions
May 14, 2009 at 4:39 am

#209399

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!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Matt Toynbee SSC Enthusiast Points: 174 More actions · Answer 2

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?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Matt Toynbee SSC Enthusiast Points: 174 More actions · Answer 4

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.