| Author |
 Topic  |
|
|
xpandre
Posting Yak Master
193 Posts |
 Posted - 11/26/2012 : 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 Name
2. By who (user), -- User ID (Preferably a Network Login ID)
3. When (time), -- Time of access
4. 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 data
6. Why (a contextual reason for access). -- Some description
I 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?
Thanks
Sam |
|
|
srimami
Posting Yak Master
152 Posts |
Posted - 11/27/2012 : 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_handle
FROM sys.dm_exec_sessions d1
JOIN sys.dm_exec_requests d2 ON d1.session_id=d2.session_id
CROSS APPLY sys.dm_exec_sql_text(d2.sql_handle) d3
Select hostname from sys.sysprocesses where spid=@@Spid
Thanks,
Sri. |
 |
|
| |
Topic |
|
|
|
Logging
