How to get the username of user that modified table

Question

How to get the username of user that modified table

jeremy.dalby

SSC Enthusiast

Points: 162
More actions
December 17, 2008 at 5:38 pm

#197324

Hi...
I'm using triggers to keep track of all changes made to a table by copying each inserted/modified row into a separate audit table that has all of the same columns plus the datetime in which the change was made and the username of the person that did it.
Everything is working well, except I can't seem to get the USER_NAME function to return anything other than "dbo", and it's driving me nuts!
USER_NAME gives the same result whether I add a row to the table directly from Visual Studio ro whether I use my application to do so.
I am using Windows authentication to connect to the database, so I was hoping that the USER_NAME function would return the username that I use to log into my PC. Seems I am mistaken.
Somebody help me, please, before I go completely insane. :w00t:
Thanks,
-jeremy

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

You want to use one of these two functions: suser_name(), suser_sname(). You might want to read up on them in Books Online to see what they do.

Trybbe Hall of Fame Points: 3967 More actions · Answer 2

I use the code below and I get the command which was used to modify, the time, the hostname from where the command was issued and the loggin name (Or in my case the employee number which was used)

USE MYDB

GO

CREATE TABLE MYDB_AuditLog

(ID INT PRIMARY KEY IDENTITY(1,1),

Command VARCHAR(1000),

PostTime VARCHAR(24),

HostName VARCHAR(100),

LoginName VARCHAR(100)

)

CREATE TRIGGER Audit ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

DECLARE @data XML

DECLARE @cmd VARCHAR(1000)

DECLARE @posttime VARCHAR(24)

DECLARE @spid VARCHAR(6)

DECLARE @loginname VARCHAR(100)

DECLARE @hostname VARCHAR(100)

SET @data = EVENTDATA()

SET @cmd = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(1000)')

SET @cmd = LTRIM(RTRIM(REPLACE(@cmd,'','')))

SET @posttime = @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'VARCHAR(24)')

SET @spid = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'varchar(6)')

SET @loginname = @data.value('(/EVENT_INSTANCE/LoginName)[1]',

'VARCHAR(100)')

SET @hostname = HOST_NAME()

INSERT INTO dbo.MYDB_AuditLog(Command, PostTime,HostName,LoginName)

VALUES(@cmd, @posttime, @hostname, @loginname)

GO

SET@cmd = LTRIM(RTRIM(REPLACE(@cmd,'','')))

I Hope this resolves your problem

jeremy.dalby SSC Enthusiast Points: 162 More actions · Answer 3

Thanks Lynn and thatok for you help.

I've opted to just replace USER_NAME() with SUSER_NAME() and everything is now working the way I want it to.

Yesterday's frustration was brought to me by the letter "s"!