Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
xpandre
Posting Yak Master
212 Posts |
Posted - 2012-11-26 : 22:21:26
|
Hi,We have 1 client requirement where in every select on a particular column on a table needs to be logged.Below are the details needed.1. The specific items of data accessed (but not the data itself), -- Field Name2. By who (user), -- User ID (Preferably a Network Login ID)3. When (time), -- Time of access4. Where (an indication of physical location), -- Most likely IP address of the client (browser or workstation)5. How (the system and subsystem used to access the data) and -- Name of system that accesses the data6. Why (a contextual reason for access). -- Some descriptionI was planning to use sql 2008 Auditing feature.Now the issue is with the IP address..we have a system where in UI calls web services, which in turn calls DB. So it would be practically impossible for us to get the IP address of the UI machine, unless it actually passes it to the web service.Do we have any way in sql server we can log all these information?ThanksSam |
|
srimami
Posting Yak Master
160 Posts |
Posted - 2012-11-27 : 02:16:34
|
Hi, The following query gives the details as per your post plus some additional details. Schedule this sql using windows batch file to run every minute/half hour/hour depending on the requirements and capture the queries in .csv/.txt file using Sql Cmd. SELECT d1.session_id, d3.[text], d1.login_time, d1.login_name,d2.wait_time,d2.blocking_session_id, d2.cpu_time, d1.memory_usage,d2.total_elapsed_time, d2.reads,d2.writes,d2.logical_reads,d2.sql_handleFROM sys.dm_exec_sessions d1JOIN sys.dm_exec_requests d2 ON d1.session_id=d2.session_idCROSS APPLY sys.dm_exec_sql_text(d2.sql_handle) d3Select hostname from sys.sysprocesses where spid=@@SpidThanks,Sri. |
|
|
|
|
|
|
|