SQL Server Performance

Auditing SQL Query Analyser

Discussion in 'General DBA Questions' started by Vishu_75, Oct 19, 2005.

  1. Vishu_75 New Member

    Is there any way of auditing which user has executed the sql statements on query analyser ?
    other than using the sql profiler. Kindly let me know
  2. mulhall New Member

    What is the ultimate goal you are trying to achieve here?

    Is it the tool, the statement, particular data modification...?

    I find it hard to believe you are only interested in particular statements submitted through QA.
  3. ghemant Moderator

    Hi,
    if your management allow use Third Party Audit Tool like Coefficient , or may use trigger if application is not that write intensive.


    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
  4. FrankKalis Moderator

    Sounds like you're on a witch hunt for someone who fired some DROP or DELETE statements. If so, this is probably a good moment for review your security and permission policies.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  5. satya Moderator

    If the damage was already happened then there is no way you can track and in order to get it back you need to restore the transaction log before that process.

    As suggested above you may need to deploy third party tools for granular auditing or continue using PROFILER for further information.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. ghemant Moderator

    Hi,<br /><br />1). use strong password mechenism<br />2). Create role <br />3). Grant proper permissions<br />4). Map / add users to Role<br />5). Set Audit On for Failurs<br /><br />HTH<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami<br />
  7. satya Moderator

    The above are good but it doesn't fetch the information required about who is the culprit, so to find out you have to depend upon third party tools.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. ghemant Moderator

    Hi,
    yes sir but steps above are necessary for precaustion so that it will not repeat again,and its very true for that one have to depend upon Third Party Tool or may use Trigger if application is not write intensive (verifying hostname & programname to audit) .


    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
  9. satya Moderator

    In this case what if the user(s) who has data_writer deletes the data without any change management, how can you capture them.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. ghemant Moderator

    Hi,<br /><br />create trigger trcDelVal<br />on pubs.dbo.realvalues<br />for delete<br />as<br />declare@host varchar(12)<br />set@host=host_name()<br />if@host='host1'<br />or@host='host2'<br />or@host='host3' -- sameway can compare values from sysprocesses for programname<br />begin<br /><br />insert into pubs.dbo.tracedvalues select deleted.firstname,deleted.lastname,deleted.city,deleted.systemname,deleted.systemdate,'D' from deleted<br /><br />end<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami<br />
  11. mulhall New Member

    Wow.

    Vishu posts almost no information and you guys have unending solutions.

    What happened to figuring out the problem, before giving a solution?

    ...
  12. Vishu_75 New Member

    My requirement was to capture the login details of the user and the sql statments they execute on query analyser. Is there any system tables which caputres these details
  13. ghemant Moderator

    Hi,
    then you need to use dbcc inputbuffer(spid) ,otherwise run PROFILER !


    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
  14. gkrishn New Member

    There is no such system table captures users and the query they executed.Had this machanism implemented,it may require more space than actually what data you have <img src='/community/emoticons/emotion-1.gif' alt=':)' /> .So better you go for some 3rd party tools.<br />Or<br />Create a table,and implement some usertracking procudure which captures users,login time,duration,last login etc ...<br /><br />-Rajiv
  15. satya Moderator

    SQL server doesn't store such information on the tables and only place is in Transaction log, again using third party tools you can read the log for auditing purpose. If this is a past event then there is no chance and for future reference refer to the tips above.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  16. ghemant Moderator

    Hi,

    try using Coefficient its a very good tool for such kind of captures.

    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
  17. mulhall New Member

    We all know Profiler is the tool for the job. Now, why don't you want to use it?
  18. Vishu_75 New Member

    In a live database which has huge number of transactions(banking) taking place. I cannot use a profiler which affects the performance of my sqlserver database right ??.
  19. FrankKalis Moderator

  20. mulhall New Member

    Tracing will have a performance impact, but the question is whther it is too much of an impact.

    How many transactions per second is the live system running, and what's the current peak load on resources?

    If this is seriously highly transactional, you are going to need to be pretty specific about your trace objects or you'll have an unmanageable file.

    Try testing you trace on your test environment with a simulated load. This will tell you how much your trace will impact performance, and whether you are collecting the right data.
  21. satya Moderator

    I never had any performance problems with server side traces on the SQL that was already stressed with load of performance issues. I agree with Mulhall to convene a server side trace to look for information. End of the day it is upto Vishu's reference to deploy such method or depend upon third party tools.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page