Looging all executed SQL Server Queries

Question

Looging all executed SQL Server Queries

UmaShankar Patel

Ten Centuries

Points: 1142
More actions
March 11, 2013 at 5:25 am

#290859

Hello Friends,
I am using SQL 2008 R2.
This instance of SQL Server ,many user uses.All user uses the same user sa and password.
I want to log each query running by user with following details:
User Name: may be computer name/or IP address
Query Text,
date and time of query.
Is it possible?
if yes then how?
Thanks.......
-----------------------------------
My Blog[/url] | Articles

Viewing 7 posts - 1 through 6 (of 6 total)

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

check out using a server side trace. In a really busy system capturing all inout can sometimes be difficult and may impact performance. Make sure you log to a drive with ample space, preferably not the o/s drive.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Profiler or extended events. Bear in mind you're looking at large volumes of data. I have a moderately busy server that, just by capturing what a user runs, generates 300MB of trace data an hour.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

UmaShankar Patel Ten Centuries Points: 1142 More actions · Answer 3

Thanks for your quick reply.

I dont want to use SQL Profiler.

I think ,Profile does not show system ip or system name on which query run.

Can i use any thing else like transaction log or any third party tool?

Thanks.......
-----------------------------------
My Blog[/url] | Articles

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Profiler or Extended Events (or a 3rd party tool that will use one of them)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLCharger SSCommitted Points: 1928 More actions · Answer 5

Also worth keeping in mind that username, computername can be spoofed.

If it is essential that these are properly audited, I wouldn't trust blindly what the connection details claim 😉

Cheers,

JohnA

MCM: SQL2008

Sean Lange SSC Guru Points: 286589 More actions · Answer 6

All user uses the same user sa and password.

You REALLY need to not use the sa account for people or applications. Create logins for your different people and applications so you have better control over access and security.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/