SQL Server Performance Forum – Threads Archive

Auditing SQL Query Analyser

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

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.

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
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

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)

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.

Hi, 1). use strong password mechenism 2). Create role 3). Grant proper permissions 4). Map / add users to Role 5). Set Audit On for Failurs HTH [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

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.

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
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

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.

Hi, create trigger trcDelVal on pubs.dbo.realvalues for delete as [email protected] varchar(12) [email protected]=host_name() [email protected]=’host1′ [email protected]=’host2′ [email protected]=’host3′ — sameway can compare values from sysprocesses for programname begin insert into pubs.dbo.tracedvalues select deleted.firstname,deleted.lastname,deleted.city,deleted.systemname,deleted.systemdate,’D’ from deleted end [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

Wow. Vishu posts almost no information and you guys have unending solutions. What happened to figuring out the problem, before giving a solution? …

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

Hi,
then you need to use dbcc inputbuffer(spid) ,otherwise run PROFILER !
Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

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. Or Create a table,and implement some usertracking procudure which captures users,login time,duration,last login etc … -Rajiv

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.

Hi, try using Coefficient its a very good tool for such kind of captures. Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

We all know Profiler is the tool for the job. Now, why don’t you want to use it?

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

Profiler might place too much overhead on the server, but you can use a server-side trace. Have a look here:
http://msdn.microsoft.com/library/d…us/dnsqlmag01/html/TrackingDownEventClues.asp
http://www.winnetmag.com/SQLServer/Article/ArticleID/43014/43014.html
http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsql90/html/SQLTrcRpOb.asp —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

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.

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.

]]>