asked
2005 Capture who is selecting off of a particular table.
I need to capture who is selecting off a particular table and also capture the select statement as well as what they are selecting. It would be nice if I could deny access to that row if I determine if the user should not see the data. The problem is, it is a 3rd party system that is hitting it so I have no control over the source of the select. So just logging it will be good. I have tried tracing it and that slowed the system down to crawl. So that is not a good solution. In fact, it locked me up on one trace. But it did give me information that I wanted so I could record that. I am running on a 2005 sql server and the database is in compatibility mode to a sql 2000 database. (Crazy, I know) But it is what I have to work with. Any ideas or suggestions would be greatly appreciated. Thanks.
I was able to do something like this. SELECT * FROM master..sysprocesses WHERE spid = @@SPID I was able to find out the user by using the above select inside my view. I had to first set the user up on the database and I was able to grab the login name. The third party software was using a view instead of the a table. So I was very happy for that. So I am able to block the ones I needed based on that select and compare the login name. My user who is the manager of payroll is very happy. So I did good on Friday the 13th. Thank you Grant for the help.
Sounds like a good solution. You should mark your answer as the one that solved it.
2005 limits your choices pretty severely. If it was 2008 or better you can look to extended events, but those are not an option. And, since we're talking SELECT we can't look to INSTEAD OF triggers. Best bet of your remaining options... actually only option I can think of, is to create a view with the same name as the table and rename the table. That way the code won't have to change, but you can check security within the view.
The only way to find out who called it would be to use a server-side trace.
15 People are following this question.
