question

randym avatar image
randym 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.
sql-server-2005select
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

randym avatar image
randym answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sounds like a good solution. You should mark your answer as the one that solved it.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That is a good idea. Would I be able to find out what user called the view then? Is there a good way to do that? Thanks.
0 Likes 0 ·
The only way to find out who called it would be to use a server-side trace.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.