| Author |
Topic |
|
sandesh_moghe
Constraint Violating Yak Guru
310 Posts |
 Posted - 2002-08-13 : 22:23:37
|
HiWhen i use the following query to track the user activities select p.spid, p.uid, u.name, login_time [LoggedIn Time],p.dbid, d.name, hostname,Program_name [Application Name], p.suid from master.dbo.sysprocesses p join master.dbo.sysxlogins u on u.sid = p.sid join master.dbo.sysdatabases d on p.dbid = d.dbidorder by hostname sometimes it show null in program_name column, why? Becoz that user is connected to the database using some program, like QA,EM or front end.........Please help, thanz in advance.---------------------------Sandesh - The Messanger |
|
|
sandesh_moghe
Constraint Violating Yak Guru
310 Posts |
Posted - 2002-08-14 : 22:38:59
|
| Secondary question for the same....Is the history of sysProcesses table stored anywhere in the systable?if not can we do that?I tried to write a trigger on sysprocesses table to store the history to another table, but it is not allowing me to write trigger on systable.....Thanx in advance.---------------------------Sandesh - The Messanger |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-14 : 23:16:36
|
| You can also use sp_who and sp_who2 to get user/process info.If you want to save activity history the best way is to use SQL Profiler. You can log trace information in a table or a file. Books Online will get you started with Profiler.You cannot create triggers on system tables and even if you could, you risk the stability and proper function of your SQL Server. DO NOT MODIFY SYSTEM TABLES. |
|
|
|
sandesh_moghe
Constraint Violating Yak Guru
310 Posts |
Posted - 2002-08-15 : 02:27:48
|
| Thanx Rob.Can i keep profiler open for 24 X 365 days?will it affect server speed?---------------------------Sandesh - The Messanger |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-15 : 08:11:14
|
| Yes, you can leave it running all the time, and yes, it will slow the server down, but if you are only tracing a few things (5-6 at most) then it won't be too bad.What do you need to trace about your users? Is someone abusing their privileges? |
|
|
|
sandesh_moghe
Constraint Violating Yak Guru
310 Posts |
Posted - 2002-08-15 : 22:08:19
|
| Yes, someone is abusing his/her privileges.He/She ran batch in Query analyser, which corrupt the valuable data, i fixed the problem, but thats the main reason iwant trace for user activities.---------------------------Sandesh - The Messanger |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-16 : 08:24:19
|
| Do yourself a favor and cut off their privileges right away. Give them SELECT only, if that. You're only locking the barn AFTER the horse has been stolen by trying to monitor what they do. Consider this: sure, you can find out WHAT they did, but if something is permanently FUBAR and can't be recovered, knowing who did it is small consolation.An ounce of prevention...I should also add that a good hard smack (revoked privileges) done right away, even if it is overkill, will send a clear message to EVERYONE about what's acceptable usage and what's not.Edited by - robvolk on 08/16/2002 08:32:01 |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2002-08-16 : 08:45:34
|
| When I hear about your problems Sandesh, I can't help but feel lucky, since our users don't have any idea what SQL is. Most of them don't even know what MS Access is :) |
|
|
|
sandesh_moghe
Constraint Violating Yak Guru
310 Posts |
Posted - 2002-08-16 : 21:53:33
|
| Thanx Rob and Anthraax,In India everybody knows what RDBMS is and How can we execute the query in Query analyser (is it drawback of literacy?)See, the user who did this may not have the intension to corrupt the data, but yes Rob, you are right, why should we as DBA wait for such a thing.Also, i have started one trace which was readymade in the wizard "Trace Transact SQL Activity by user". is this enough? or will it be heavy for server? or should i add some data columns in it?Thanx in advance.---------------------------Sandesh - The MessangerEdited by - sandesh_moghe on 08/16/2002 21:54:49 |
|
|
|
|
Tracking User Activities.