Who is the SQL User who deleted row from table

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Who is the SQL User who deleted row from table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-08 : 07:31:58

Toby writes "OK I am new to all of these so please bare with me.

Using SQL server 2000
Users are setup with own SQL authentication Logins not just sa.

I am using a simple trigger on a table (A) that I have that when a row from this table is deleted it updates another table (B) with the data that was deleted from the original (A).

I have an extra column in table B that is called USerid. I want to populate the Userid Column with the name of the SQL user who deleted the original row from Table A.

For example when the row is deleted from table A it is inserted in to table B and the SQL userid is updated for that row in tbale B to reflect who deleted the individual row from table A.

Table A does not have any userid's associated to it within the data.

I have tried to get the name of the SQL user who has deleted this row. I have had a look at sysprocesses, sp_who and sp_who2 but to no avail.

People have mentioned on the net in other new groups to use the Profiler or go to the lumigent website. But I really need to see if it is possible to get it through TSQL.

Has anyone any ideas.

Regards Tobe"

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-09-08 : 07:59:23

Writing the trigger should be no major deal. Getting the User who deleted the row is a bit tricky, depending on the authentication mechanism used by your program. If you are using Windows Authentication you can use SYSTEM_USER to determine the user, USER for Sql Server Authentication, and if you are using your own authentication mechanism in addition to one of the above, you will need to pass it explicitly by some means from your front-end. Assuming you are using Windows Authentication:


CREATE TRIGGER del_Trigger ON TableA
FOR DELETE
AS 
BEGIN

 INSERT INTO TableB(UserID, DateTimeDeleted, ColA, ColB, ColC)
   SELECT SYSTEM_USER, CURRENT_TIMESTAMP, ColA, ColB, ColC
   FROM DELETED d

END

Owais

Make it idiot proof and someone will make a better idiot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources