Please help for auditing

Question

Please help for auditing

rahul rathi

SSC Enthusiast

Points: 160
More actions
July 8, 2007 at 8:44 am

#62067

Hi,
I am new to sql server. I would like help from you gurus out their. I wanted to audit the activites (DML) on my database. I wanted the audit to achive the following
1] Audit specific tables for the DML activities.
2] capture the details of the username, time and system from which this query executed.
3] Can i get the changed and the previous values.
4] I don't want to use the triggers.
I did read in some articles this is achivable with the help of SQL-profiler and osql but don't know the exact steps. Could some one provideme with the step by step guide to get this done?
Please help,
Rahul.

Viewing 15 posts - 1 through 15 (of 28 total)

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

Yes you have to use profiler to do this. You can start profiler from the programs menu move to SQL Server and start from there. You have plenty many options with profiler and exclusive help is available with BOL.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

rahul rathi SSC Enthusiast Points: 160 More actions · Answer 2

hi Sugesh,

As i said it earlier i am a novice with sql server would appriciate your help on this. I know thier are lot of options with profilier but don't know what will suit my requirements. and what is BOL?

thanks,

Rahul.

Sachin Samuel-286731 Right there with Babe Points: 778 More actions · Answer 3

Hi,

Check this url below.

http://www.sql-server-performance.com/sql_server_profiler_tips.asp

The above article will help you to understand the events you should add.

BOL= Books online. Its the SQL server help files.

Thanks,

Sachin

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 4

Rahul,

goto profiler then move to events tab,

Select stored procedure - spcompleted, stmtcompleted.

select tsql - stmtcompleted.

move to filters and give the name of the database that you want to monitor

you can save the data to a file or to a table and those will be avialble in general tab.

for anyhelp let me know i shall get back.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

rahul rathi SSC Enthusiast Points: 160 More actions · Answer 5

Thanks Sugesh/Sachin,

I atleast got started. I see some data getting in the trace.

I am having the following issue.

The data which is getting inserted through application is not proper. I see only the actual query and not hte values.

i.e I am seeing only parameters in the queries and not the actual values which are getting passed. How do i get this? Can you help solving this problem?

Thanks,

Rahul.

rahul rathi SSC Enthusiast Points: 160 More actions · Answer 6

I had one more question, how do i modify the rules for the existing trace file?

thanks,

Rahul.

Sachin Samuel-286731 Right there with Babe Points: 778 More actions · Answer 7

Stop the trace and click on the Edit icon on the top. Move your mouse over the icons on the top of screen and click on the icon having tool tip "Properties".

Regards

Sachin

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 8

I hope you will bnot be able to capture the values. Anyways let us see what other users have on thier opinion to best of my knowledge we can trace the sql statements and not the values being passed.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Sachin Samuel-286731 Right there with Babe Points: 778 More actions · Answer 9

We can see the values in profiler. Please tell us what steps you have performed to execute the profiler. What all rules you have applied?

Sachin Samuel-286731 Right there with Babe Points: 778 More actions · Answer 10

Steps!

1) Connect to the server where you want to trace.

2) In the general tab, select SQLProfilerStandard.tdf as template file name for trace. (by default this is the one selected)

3) In the event tab remove security audit, sessions.

4) In filter tab, put any filter you have like database id (incase you want to trace any specific database. You can get it by quering "select Name,Dbid from master..sysdatabases"). Copy and paste it in "like" section of Database id.

5) Check the checkbox "Exclude system ids".

Thats all. Now run a query and see if you are able to trap. And then run you application. You should be able to trace, the queries with there Values!

Thanks

Sachin

Sachin Samuel-286731 Right there with Babe Points: 778 More actions · Answer 11

Don' know why my previous is not appearing so reposting it again. I am sorry if later it appers twice.

****************

Steps!

1) Connect to the server where you want to trace.

2) In the general tab, select SQLProfilerStandard.tdf as template file name for trace. (by default this is the one selected)

3) In the event tab remove security audit, sessions.

4) In filter tab, put any filter you have like database id (incase you want to trace any specific database. You can get it by quering "select Name,Dbid from master..sysdatabases"). Copy and paste it in "like" section of Database id.

5) Check the checkbox "Exclude system ids".

Thats all. Now run a query and see if you are able to trap. And then run you application. You should be able to trace, the queries with there Values!

Thanks

Sachin

*****************

rahul rathi SSC Enthusiast Points: 160 More actions · Answer 12

Hi Sachin,

I have done the steps and seems to work fine. but want can be done for updates mean how do i capture the before image? Is it possible.?

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 13

Sachin,

i too was meaning this. I wanted to know what was the value before change and after change and if that is possible with profiler. I know that passing the values will be captured in profiler i wanted before and after image of the data. as what ahul wants. is this possible.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Sachin Samuel-286731 Right there with Babe Points: 778 More actions · Answer 14

But the question from Rahul says that he not able to see the values while tracing. :s.

Rahul, Trapping the older values thru profile is certainly not possible.

But the 2 way which I can think of is

1) Use triggers.

2) Use a tool called Log explorer, which can be used to read sql log files.

Google it, you will get more details abt the point 2.

Thanks

Sachin