How to monitor the executed SQL in SQL 2005

Question

Post reply

How to monitor the executed SQL in SQL 2005

h_maksuda

SSC Eights!

Points: 966
More actions
December 11, 2009 at 3:12 pm

#214534

Hi:
I have project running on SQL 2005. There 2/3 users only have delete permission and 4 users have update permission. Now I want to monitor the activities of the user on my database, when and which user delete/update data from which table.
How can I do that using SQL 2005's help.
Need help please
Thanking You,
Maksuda...

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Might I suggest you use Books On Line

SQL Server 2005 Books Online (September 2007)

SQL Server Profiler Terminology

It will explain how you can set up a trace and have the trace place the data into a table for later examination.

If you attempt this and come up with a specific problem re-post and some one will attempt to assist you.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

Absolutely follow BitBucket's advice.

Just don't run the Profiler GUI directly against any of your production systems. Only use the server side trace (TSQL scripts) against production systems.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

We have an eBook in our Books section[/url] on Profiler. Worth reading to understand things, but as Grant mentioned, use a server side trace.

h_maksuda SSC Eights! Points: 966 More actions · Answer 4

Thank BitBucket. I read SQL 2005 online books about Profiler, but which event is for tracing delete event is not cleare to me.

Can you help for finding this.

Thanking You,

Maksuda...

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 5

Under TSQL

Choose the 2 events SQL:BatchCompleted and SQL:BatchStarting

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

h_maksuda SSC Eights! Points: 966 More actions · Answer 6

Thanks for the reply.

SQL:BatchCompleted and SQL:BatchStarting are not for tracing delete event right? In that case server performance may go down and also it'll be very tough to find the delete only.

Is there any other way for tracing?

Thanking You,

Maksuda

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 7

You can write triggers to catch updates and deletes on specific tables if you want.