trace the computer name
-
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.
-
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.
-
I think you are looking for HOST_NAME() http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ha-hz_05r9.asp[^]
-
Thanks, but this give me the computer name when a row is added, when a row is updated or deleted nothing is logged. I can't know who update a specific record. any other idea. thanks again.
I have solved this problem in a different way. When the user logs in to the application, I insert the kerberos user id and the hostname in a Activity table. But there is a limitation. One user cannot start two instances of the application from the same host. So when there is a update or delete or insert, I pick up the value from this table. Everytime the user logs in, I clear the Activity for that user in a host machine. CREATE PROCEDURE ValidateUser ( @userid varchar(10) ) AS DECLARE @cnt int BEGIN DELETE FROM Activity WHERE Host = host_name() INSERT INTO Activity VALUES(host_name(),@userid) SELECT @cnt = count(*) FROM Users WHERE ID = @userid IF @cnt = 1 SELECT 1 ELSE SELECT 0 END In the trigger I do something like IF @@rowcount = 0 RETURN SELECT @user = CurrentUser FROM Activity WHERE Host = host_name() IF UPDATE(Column1) INSERT INTO AuditTrail SELECT @user,getdate(),'Table1','Column1' --blah blah FROM deleted,inserted WHERE ------------------- Does this help.