Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

I am using MS SQL Server 2008. I have a table in a database, in which records are being inserted and deleted.

E.g.

Initially the table is empty. A Java program (probably) inserts few records in it. Another Java program quickly access the table and deletes the records (All the records which were inserted). And then I manually run a select query on the table. So basically I can see only the "initial" and final state of the table, both of in which the table is empty.

I need to know that is there a way by which I can see those records, or at least check if something is being inserted/deleted?

Any help is appreciated.

share|improve this question
Is this information that you want in real time? – Maess Nov 30 '11 at 20:28
I don't know what do you mean by 'real time'. But I have just added an example of my scenario. Please refer the updated question. Thanks! – Bhushan Nov 30 '11 at 20:44
I guess what he means is whether this is an application requirement (i.e. do users need to audit this data) or whether you want it for debugging purposes. – ConcernedOfTunbridgeWells Dec 1 '11 at 13:44
Got it, I want it for debugging purposes – Learner Dec 1 '11 at 14:18
1  
In that case the answers talking about SQL profiler are probably the best approach for your purposes. – ConcernedOfTunbridgeWells Dec 1 '11 at 14:30

migrated from stackoverflow.com Nov 30 '11 at 20:48

3 Answers

up vote 4 down vote accepted

You need to be granted the necessary permissions on the table that contains the audits. The minimum for you to achieve what you want is you need to be granted SELECT on that table.

As far as check if something is being inserted/deleted, you could always create a trace with SQL Profiler. At the same token though, you need ALTER TRACE permissions on the server, and if you don't have the former SELECT permission I doubt you have ALTER TRACE.

share|improve this answer
I do have the select privilege, how can I do what I am trying with that? Also, I am using MS SQL Server Management Studio. Is Profiler part of that? – Bhushan Nov 30 '11 at 20:47
@Bhushan Profiler is a separate tool, although can be started through SSMS. Profiler doesn't ship with all editions, you must have Standard or up. But you can still create a trace (although a bit more in depth). – Thomas Stringer Nov 30 '11 at 22:00
If you have select on that table, are you not able to select * from yourAuditTable?? – Thomas Stringer Nov 30 '11 at 22:00
The OP doesn't have an audit table, that's the problem :) – Mark Storey-Smith Dec 1 '11 at 13:11
Mark is right, I don't have an audit table and neither have permissions to create one. I guess by audit table you mean a manually created table in which I have make entries manually for each operation I perform? – Learner Dec 1 '11 at 14:58
show 1 more comment
up vote 3 down vote

I second the SQL Profiler suggestion if this is for short-term and you are looking for future activity.

For long term, I'd consider creating a log table and use triggers on the table you want to monitor to feed the log with the info you want.

If you need to know what happened in the past, you might be able to get that information from the transaction logs. I know there are 3rd-party tools to allow you to do this, but I couldn't say for sure if you could track a transaction to a specific individual this way.

share|improve this answer
up vote 3 down vote

SQL Profiler is probably the preferable option if you want to capture this activity over a short period. If you're feeling adventurous or are trying to do this retrospectively, you could always crack open the transaction log for evidence.

If this facility is required long term, audit triggers are a valid suggestion. If you're using Enterprise Edition, Change Data Capture would be preferable to triggers.

Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.