Please help for auditing

  • 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

  • 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.

  • 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

  • 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?

  • 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!

     

  • 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.

  • 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

  • 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.

     

  • 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

  • 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?

  • 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

  • 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.

     

     

  • 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

  • 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

Viewing 14 posts - 16 through 28 (of 28 total)

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