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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Logging

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 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

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_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.
Go to Top of Page
   

- Advertisement -