SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Logging
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

xpandre
Posting Yak Master

199 Posts

Posted - 11/26/2012 :  22:21:26  Show Profile  Reply with Quote
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 - 11/27/2012 :  02:16:34  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000