May 30, 2008 at 1:49 pm
Hey Guys,
My manager wants me to track specific users. He also wants me to know what all stuff they do at certain time. Problem I have is, we have user ID which is being used by 3 people. I would like to know how can I track all the 3 users and also time and system information, mean system on which they are working. Is there a way where I can also track history information for all the three users. Please let me know as he wants it real fasst. Anything would be appreciated
Thanks in advance
May 30, 2008 at 4:48 pm
Only option would be SQL trace (profiler) or check for third party tools...
MohammedU
Microsoft SQL Server MVP
May 31, 2008 at 2:35 pm
On SQL Server 2005 SP2 you can track login activity (when they connect) using Login triggers. Otherwise, your only option is, as previous stated, using SQL Server Profiler or a server-side trace.
Since three people are sharing an account, however, that will only tell you what the account is doing. You won't be able to definitively tie actions to one particular person. If at all possible you should convert them all to connecting via separate accounts. Otherwise, from a security perspective they will have repudation (the ability to say, "It wasn't me,") unless you have some other means to tie them to the action.
K. Brian Kelley
@kbriankelley
May 31, 2008 at 10:20 pm
Third party tool like SQL Auditing from apex is not bad, however, what about using the host name from the connection string such as the computer name? Have a look to sql server Dashboard reports.
There you can find a lot of information about what is happening on your system..plus is freeeeeee:D
May 31, 2008 at 10:28 pm
Host name is passed by the client and therefore easily faked. It's in one of the articles of mine on the site, I think the one on login weaknesses. I can be done with a File DSN, for instance.
K. Brian Kelley
@kbriankelley
May 31, 2008 at 10:37 pm
I do agree on the weakness of using the host name, however, we are making assumptions of how the users are connecting to the database, are they using an application? are they just writing queries using the sql client? like I stated before a good example of what, how, and when can be done by a simple trace or using the dashboard performance tunning which have a lot of information about what is going on plus is free
June 2, 2008 at 12:11 pm
I don't disagree that typically a user wouldn't resort to this, however, if you want something iron clad, this isn't the way to go. For instance, it would likely not hold up legally because of the fact that the hostname can be forged. Separate user IDs can be, so long as it can be shown that they weren't shared.
K. Brian Kelley
@kbriankelley
June 2, 2008 at 12:47 pm
Going forward, we kind of loose track of the actual question for the post. In conclusion:
1. A sql trace or profiler can be use to track what is been execute.
2. Third party tool i.e. Sql logs by apex which kind give you auditing capability in a less intruded matter, however, there are many tools to do this.
3. Microsoft Dashboard performance tool which add reporting capability to the server giving a set a report for tuning and auditing.
June 2, 2008 at 12:56 pm
Jorge Novo (6/2/2008)
Going forward, we kind of loose track of the actual question for the post. In conclusion:1. A sql trace or profiler can be use to track what is been execute.
2. Third party tool i.e. Sql logs by apex which kind give you auditing capability in a less intruded matter, however, there are many tools to do this.
3. Microsoft Dashboard performance tool which add reporting capability to the server giving a set a report for tuning and auditing.
And none of these stand up in court unless you get rid of the repudiation issue. 🙂
K. Brian Kelley
@kbriankelley
June 2, 2008 at 1:37 pm
Thanks a lot for al the suggestions. But my case all the databases are in simple recovery mode. So even if I trace it, it is difficult. Though I figured a way out. It is the enable C2 audit server option.
June 2, 2008 at 2:11 pm
toparsi (6/2/2008)
Thanks a lot for al the suggestions. But my case all the databases are in simple recovery mode. So even if I trace it, it is difficult. Though I figured a way out. It is the enable C2 audit server option.
C2 audit does a very detailed server trace. And you'll see about a 20-30% performance hit, if I remember the estimates I read. Plus you're going to need a lot of disk space. You can write a less intensive server trace. You can use SQL Profiler to build the trace.
K. Brian Kelley
@kbriankelley
June 2, 2008 at 2:25 pm
yes,
But all my databases are in simple recovery mode. Can we get all the information from the logs in simple recovery mode? I dont think so. As I cant change all my databases from simple to full and I need detailed logging, I think this is the only option. Any ideas would be appreciated.
June 2, 2008 at 2:52 pm
C2 audit is nothing more than a server-side trace with a lot of the events and columns turned on (actually most of them). You can build a more lightweight trace that will do what you want. The best tool for that is the Profiler tool that came with SQL Server. It'll allow you to select what events to monitor and what columns of those events to record data on. Then you can run the trace to make sure it's capturing what you want. When you've accomplished that, you can have Profiler create a server-side trace for you. Basically it does all the T-SQL to set up the trace. If you put this in a stored procedure in the master database and then toggle that stored procedure to start when SQL Server starts, you've enabled detailed tracing. This is basically what C2 auditing does.
In this respect, since we're talking about tracing, it does not depend on your recovery models. If you're not familiar with traces, take a look in Books Online for the following topic: Introducing SQL Trace. You can get to it via SQL Server 2005 Books Online | SQL Server Database Engine | Administering the Database Engine | Monitoring and Tuning for Performance | Monitoring for Events | Introducing SQL Trace.
K. Brian Kelley
@kbriankelley
June 2, 2008 at 3:55 pm
thanks a lott brian... I dint know all dat..
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply