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 |
|
Goodway
Starting Member
3 Posts |
 Posted - 2005-12-07 : 10:17:36
|
| hi everyone,In my company, 4 users are accessing the sql server with a username and a password, they are developers, they have rights to create tables, fields, insert rows, deleting rows,...how can I trace which computer make an insert or delete.for example if a user make an insert into a table from SQL Server enterprise manager by writing an insert query, how can i know which computer make this action?is that possible?thanks. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-12-07 : 10:34:59
|
| Are you saying that your 4 developers all use the same username and password? Are you also saying that the developers have access to production sql servers? Typically, everyone uses their own login access the servers. Access to production data should be limited to "operations" people. The only 2 ways I can think of right now to caputure the user's machine name are to leave a profiler trace running (not a good idea on production) or create triggers for auditing on the tables you are interested in. Within the trigger code you could get the machine name of the user with this statement:select hostname from sysprocesses where spid = @@spidBe One with the OptimizerTG |
 |
|
|
Goodway
Starting Member
3 Posts |
Posted - 2005-12-09 : 11:49:36
|
| The 4 developpers are all using the same sql username and password,not on the production database.it is a testing database server but i need a quick way to know who did an insert on a specific table , who did an update, who did an insert.thanks. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-12-09 : 12:03:33
|
>>but i need a quick way to know who did an insert on a specific table Here's a low-tech alternative that a surprising number of people forget to try:Ask them.  Be One with the OptimizerTG |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-12-10 : 13:19:21
|
| Are they doing these operations with Query Analyzer or some other tool?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-12-10 : 14:48:46
|
quote:
Originally posted by Goodway
The 4 developpers are all using the same sql username and password,not on the production database.it is a testing database server but i need a quick way to know who did an insert on a specific table , who did an update, who did an insert.thanks.
I dont think this is a good idea. As Derrick asked what tool are they using to access sql server ?I would advice you create different accounts for each user. This would make things much smarter, easier and less troublesome.If you have to create triggers to monitor everything done, once you have finished yoru tests, you would have mouse traps in your dbBut most important what tool are you accessing sql from ? |
|
|
|
JGambolputty
Starting Member
1 Post |
Posted - 2005-12-22 : 07:51:26
|
| Come on guys, how about just answering the question. Advice is always appreciated, but answer the question as well.Anyway . . .In SQL Profiler, go to trace properties. Select the Data Columns tab and add HostName to the Selected Data. The trace will now tell you what machine the user was on when the action occurred.Cheers,-Johann |
|
|
|
|
|
|
|
|
trace the computer name