Getting UserName from within Trigger or SP

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

Transact-SQL (2000)

Getting UserName from within Trigger or SP

Author

Topic

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-09-02 : 10:45:34

SQL Server 7....

I have some tables that store deleted records in case we need to undo any 'erroneous' deletes. These work fine, however, I want to be able to store the name of the user who deletes the record as well and the date it was deleted.

Is there someway from within a trigger or SP i can get the username of whoever deletes a record ?

I know i can do this through VB6 (the front-end) but was hoping to contain this functionality to within a delete trigger.

Everyone has their own SQL Server login that resides within a role.

Also, what's the best way to learn about the system tables and the information they contain ? Is there a great book out there somewhere or is good old fashioned hands-on the best ? Many thanks Paul

Here is a typical delete trigger as it stands now without the username bit. The delete table is the same as the live table.


CREATE TRIGGER Plc_Address_DTrig ON dbo.Plc_Address FOR DELETE AS
/*
* STORE deleted record in del_Plc_Address table
*/
INSERT INTO del_Plc_Address
SELECT deleted.*
FROM deleted

Paul

Edited by - KnooKie on 09/02/2002 11:00:35

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-02 : 11:13:09

Depends how you connect to the database but system_user is a good try.

If you connect as a fixed user ID then the system doesn't know it.

You can look at bol for the info.
sysprocesses table holds most of the info to do with the spid.
Also worth looking at system SPs (sp_ in master and msdb) to find things.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ashok
Yak Posting Veteran

57 Posts

Posted - 2002-09-02 : 12:03:23

I recently did something similar - though i used host_name() instead of user id.
that seems to work fine.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-02 : 12:12:12

that should give the name of the client machine that is connected (again depending on how you connect).

Other things to look at
user_name()
host_id()
session_user
current_user
app_name
......

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources