Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQL_Deepak
Starting Member
9 Posts |
 Posted - 2010-03-25 : 06:29:02
|
| HelloWe are hosting some financial applications on sql server 2005 which is very critical to business. Is there any way on sql server we can track which users logged at what particular time and what queries they performed on it. We want to even log and be able to track what the database administrator activities performed on the system.Please help me on how to go about doing.Thanx..Deepak |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-03-25 : 07:09:21
|
| There are various methods you can use, but the easiest and least visible to users is to create a trace using sql server profiler. A profiler trace will capture *everything* that goes on on the server (including system processes), it's all up to whoever configures the trace to filter out unwanted data. Setting up a trace is fairly easy, but if you have troubles feel free to ask. Be aware though that a profiler trace can generate massive amounts of logging data so you need to be careful when setting it up. Disks fill up really fast if you're not paying attention.- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-25 : 13:37:45
|
| have a look at Lumigent AuditDB |
|
|
|
SQL_Deepak
Starting Member
9 Posts |
Posted - 2010-03-25 : 18:32:10
|
| Thanx heaps....I will try this and see how it goes.Deepak |
|
|
|
SQL_Deepak
Starting Member
9 Posts |
Posted - 2010-03-25 : 20:10:16
|
| HiI have used sql profile and able to trace user logins and actually see which queries and sp they they are running. This is really awesome.Is there any way I can run these traces forever on the system without stopping for example can we run this as a service and not leaving the sql profiler window open.Also my aim here is to generate daily log files and backup it on a tape or other external places to avoid space issues.Thanx..Deepak |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-03-26 : 04:03:34
|
I've never run a sql server trace "indefinitely" before but I guess something like this would be fairly easy to implement using sql server agent. Have you noticed that if you close Profiler while the trace is running it will ask you if you want to keep the trace running or not? If you leave it running it will run until something stops it, like a manual stop or it runs out of disk space or if you set a specific stop time or something. What you could do is create a stored procedure that you schedule to run every x minutes that checks to see if the trace is running or not and if it isn't starts it. First create the trace in profiler and start it (creating a trace using code is a mess so stick to profiler for this). Then to monitor if it's running you could do something like this:IF (SELECT status FROM sys.traces WHERE id = @MyTraceID) <> 1 EXEC sp_trace_setstatus @traceid = @MyTraceID, @status = 1 http://msdn.microsoft.com/en-us/library/ms176034.aspx- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-03-26 : 04:15:59
|
| You should also know that it's possible to run the sql server agent job from another server so it won't be visible on the server you're monitoring. An administrator with bad intentions could easily see the agent job if it was running on the same server but if it's running from another server it's a different story. I've never set this up either though so I don't know the details of the inner workings unfortunately.- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
|
|
|
User activity