March 11, 2013 at 5:25 am
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
March 11, 2013 at 5:33 am
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/
March 11, 2013 at 5:51 am
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
March 11, 2013 at 6:30 am
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
March 11, 2013 at 6:34 am
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
March 12, 2013 at 8:00 am
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
March 12, 2013 at 8:17 am
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/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy