Getting current user using SQL key

Question

I have the following sql trigger for one of my tables. I'm just wondering if it is possible that I can track and add a user who is making changes to the table?

See code below

ALTER trigger [dbo].[Trigger_Audit_Client] on [dbo].[Client] for insert, update, delete as declare @type varchar(1) , @UpdateDate datetime if exists (select * from inserted) and exists (select * from deleted) select @type = 'U' else if exists (select * from inserted) select @type = 'I' else select @type = 'D' select @UpdateDate = getdate() insert Client_Audit (Client_Id, ClientName, ClientSurname, TelephoneHome, TelephoneWork, TelephoneCellular, DOB, Gender, ClientIdNumber, Company_Id, Region, City, Email, AddressLine1, AddressLine2, Will_UID, WillCreated, WillLatest, UpdateDate, UpdateType) select Client_Id, ClientName, ClientSurname, TelephoneHome, TelephoneWork, TelephoneCellular, DOB, Gender, ClientIdNumber, Company_Id, Region, City, Email, AddressLine1, AddressLine2, Will_UID, WillCreated, WillLatest, @UpdateDate, @type + '_old' from deleted insert Client_Audit (Client_Id, ClientName, ClientSurname, TelephoneHome, TelephoneWork, TelephoneCellular, DOB, Gender, ClientIdNumber, Company_Id, Region, City, Email, AddressLine1, AddressLine2, Will_UID, WillCreated, WillLatest, UpdateDate, UpdateType) select Client_Id, ClientName, ClientSurname, TelephoneHome, TelephoneWork, TelephoneCellular, DOB, Gender, ClientIdNumber, Company_Id, Region, City, Email, AddressLine1, AddressLine2, Will_UID, WillCreated, WillLatest, @UpdateDate, @type + '_new' from inserted 

Answer 1

User_Name() : http://technet.microsoft.com/en-us/library/ms188014.aspx

Syntax

 USER_NAME ( [ id ] ) 

When id is omitted, the current user is assumed in the current context. If the parameter contains the word NULL, NULL is returned. When USER_NAME is called without an identifier after the EXECUTE AS statement, USER_NAME returns the name of the impersonated user. If the Windows chief administrator accesses the database through group membership, USER_NAME returns the name of the Windows director instead of the group.

ASIDE:

I would change the startup code to this:

 SET NOCOUNT ON; INSERT INTO dbo.Client_Audit (Cliend_Id, ClientName, ..., UpdateDate, UpdateType) SELECT Coalesce(i.Cliend_Id, d.Cliend_Id) As Cliend_Id , Coalesce(i.ClientName, d.ClientName) As ClientName , ... , Current_Timestamp As UpdateDate , CASE WHEN i.Cliend_Id IS NULL THEN 'D' WHEN d.Cliend_Id IS NULL THEN 'I' ELSE 'U' END As UpdateType FROM inserted As i FULL JOIN deleted As d ON d.Cliend_Id = i.Cliend_Id; 

Does the same thing, but cleaner (without additional logic and variables, only one operator).

Any questions just ask a question

Answer 2

To do this, use system_user username () .

user_name () will return dbo if your user is in the sysadmin role.

Answer 3

Current_user is what you are looking for a good sir (depending on which version of SQL server you are using) http://technet.microsoft.com/en-us/library/ms176050.aspx

Answer 4

You can create a new table and fill it with a trigger, for example:

 create trigger LogTrigger ON YourTable FOR INSERT as insert into LogTable (Col_1, Col_2, Col_3, DataInsert, UserName) select Col_1, Col_2, Col_3, GETDATE(), User_Name() from inserted