September 10, 2002 at 2:54 pm
I have a table in a SQL2K database. I want to keep a record of what user has run a SELECT query on a certain table. I want to know who ran the query and what was the criteria of the WHERE statement. I was hoping to get a best approach idea from you.
My first thought was to use a trigger. But triggers only work with UPDATE, INSERT, and DELETE. The user is not modifying the data ~ just viewing it.
My next idea is just to insert the criteria of the search and the user name into another table ~ say an audit table. How do I determine the user that is connected to SQL Server within the INSERT statement ?
Thank you.
September 10, 2002 at 3:13 pm
Provided the user is running the select query as part of some batch of code, like a SP, then you could use something like this to capture the user connection information.
SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 10, 2002 at 4:02 pm
You could also use SQL Profiler for this. Just set up the profile trace to save to a table. Also, you will need to filter out everything but the select. To do this, when you set up the trace, go to the filter tab and go the the trace event critera called TextData. Put in 'Select%' under the "like" area.
Let me know if you have any questions.
Diane
September 10, 2002 at 4:31 pm
Alternative is to turn off table access, make them use a stored proc, you can do the logging in it easily.
Andy
September 10, 2002 at 11:46 pm
dm is correct but profiler is very costly affair.....
You can do someting like this by creating such procedure...
use select * from sysprocesses and where cmd type is select or update or insert take those spid and run dbcc inputbuffer for thoses spid's and store this result into some table. Filter sysprocesses for db also.
use with(nolock) clause with these tables. and run this procedure in every few minutes from some other database....
With this approach still you will miss the small queries but the queries taking long will caught.....
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
September 11, 2002 at 3:56 am
There are as addressed here various ways. Personally I would suggest Andy's as best, then you have some controll and can log specifically what they ran and save the input variables. Now as for SELECT if the app they use is of your own design you could build and reusable snippet of code to log their id and the select string to a data table. However, if not of your own design and you cannot go with Procedures then your only option left is to use Profiler as suggested, but you do not have to use the actually profiler in process, look at sp_trace... if SQL2K or xp_trace... if SQL7 to establish a trace query to do the same thing. Finally, you may see what they are running but if you do not have a login where each person uses their own login you may want to instead look at hostname so you can potentially track back to the source.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 11, 2002 at 8:22 am
I created a stored procedure and was able to log the data I wanted !!! Thanks !!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply