Please help for auditing

Question

Please help for auditing

Viewing 14 posts - 16 through 28 (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

ok There is some misunderstanding somewhere anyways no issues. But the first post from Rahul on this topic ask for that. Just confirmed thanks that the before and after image will not be available through profiler.

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

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

Thanks Sachin and Sugesh,

You both have been a great help.

I will clear the misunderstanding. I had both the problems. I am still looking for any possible way in SQL profiler to get the before and after image.

As far as triggers go, as i originally posted, i cannot use a trigger(constraint).

Any useful pointers on looking at the log?

Thanks,

Rahul.

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

As Sachin suggested yo can go to read the log file. But you have to have the database in FULL recovery model to have all transaction in the log file.

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

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

How do i restrict the audit to only fixed set of tables? Even if i give the list in the object name like i still keep getting all the tables ? I guess their should be some way to achive this?

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

For fixed set of tables, Trigger is the only way dear. By the way why are you so controvert for triggers? If they are properly used then there is no harm!

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

My client is very rigid about not having triggers. But i guess we should be having some way to restrict the tables getting audited. I did read, it was possible to do it for osql then we should be able to restrict it for the sql profilier too. Now i am looking at capturing the information in log files.

This shall be useful only if i get the before image of the objects.

thanks,

Rahul.

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

You want to limit the table in profiler that can be done. Filter it by objectname column like

('%table1%,'%table2%',.......................,'tablen%')

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

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

How can i read the containts of the log file. I guess Logs should have the pre and post image of the data.

Thanks,

Rahul.

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

You have to use 3rd party tools for that. There are tools available in net.

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

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

I don't want to use any 3rd party tools. If a third party tool can do it can we read it? Why is all the information in sql server so intricate?

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

I don't think so that there are any documented or undocumented procedures that can be used to read log files. The onl command that comes to my mind about reading logs is DBCC log(dbname) which will give the lsn, transaction details. Anyways will check and help you to get updated on it.

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

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

hi Sugesh,

I did try the dbcc log command. The out put it gave was a waste. It just displayed me four coloms and i didn't knew waht to do with it.

If i use 3rd party log explorer tools will they give me the pre and post images of a the updated data and the other details?

Let me know from your exp (if used).

Thanks,

Rahul.

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

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=16389&DisplayTab=Article

http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html

You have few third party tools not only log explorer. You can downloas these and try to decide which best suits for you.

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 14

Red Gate has a free "SQL Log Rescue" for SQL2000 !

Very nice !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me