BobSacks
asked on
Auditing SELECT access to a SQL Server table
I need to keep an easily accessible record of user id's and time whenever someone accesses a particular SQL Server 2005 table. I have used triggers to accomplish this in the past, to record edits, deletes and inserts and write an entry to a log table that I created. Is there a way to record a SELECT operation? Basically I need to know WHO is accessing the table and when.
In my particular situation, I'm not concerned with anyone trying to access the table through a view or stored procedure (but I'd be delighted if I knew how to monitor those operations as well).
In my particular situation, I'm not concerned with anyone trying to access the table through a view or stored procedure (but I'd be delighted if I knew how to monitor those operations as well).
There a many vendors who produce software to do this. Apex and DB Audit are two I can think of off the top of my head. With those you can view pretty much everything that goes on on the server.
ASKER
Yeah... It's a one-time short-term need. My client is unwilling to purchase anything. I was hoping that there was an easy way to do this.
The only other possibility I coudl see was to use Profiler and limit your filter to select statements. I done use Profiler often but the microsoft guide is here: http://solidqualitylearning.com/blogs/dejan/archive/2004/11/25/214.aspx
ASKER
That link doesn't work for me. Does it work for you??
Tell your client there is no silver bullet, they either pay for the software or pay you.
The best you can accomplish is to limit all access to stored procedures. You can then log all you need...
The best you can accomplish is to limit all access to stored procedures. You can then log all you need...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Not exactly what I was hoping for.. but damned interesting!!! I've never used the Profiler before. I'm not sure it will do precisely (and simply) what I want, but I sure do want to play with it!! Thanks!
Welcome!
>>I'm not sure it will do precisely (and simply) what I want<<
Hate to break it to you, but no, it will not. SQL Profiler is a dubugging and/or ad hoc monitoring tool not an auditing utility.
Hate to break it to you, but no, it will not. SQL Profiler is a dubugging and/or ad hoc monitoring tool not an auditing utility.
ASKER
Darn! Got any other ideas?
From my last comment:
"Tell your client there is no silver bullet, they either pay for the software or pay you.
The best you can accomplish is to limit all access to stored procedures. You can then log all you need..."
"Tell your client there is no silver bullet, they either pay for the software or pay you.
The best you can accomplish is to limit all access to stored procedures. You can then log all you need..."