username on trigger, different behavior on insert and update 1

[kaijanm]

Hello, Thanks in advance.

I'm converting an access front end to our SQL Server backend over to .net and as part of this process, I'm creating all the queries on the server. We have triggers on most of our tables that say who last updated the record and when.

I use SPROC's for everything.

If a user CHANGES a record on SQL server, it correctly lists their name on the table.

If a user INSERTS a record on SQL server, it lists dbo as their name on the table.

Any idea how to get it to always show the users name?

Thanks!
Kimberly

 

[SQLSister]

What are you using now to to get the username? There are severl function s which can return users. Which one are you using in each trigger?

Questions about posting. See faq183-874

 

[kaijanm]

The function we use is suser_sname(). Does that affect this?

Thanks!

 

[SQLSister]

You are using it in both in the insert and update triggers?

According to my test suser_sname() should get the correct name for you.

This select will give you the resluts for the various differnt ways of selcting the username, maybe you can use it to find which one does what you want. Log in to query analyzer using the login that users use when they insert or update data and run it to see which gives you what you want. MAke sure to try people who are and are not daatbase owners.

select CURRENT_USER as currentuser, SYSTEM_USER as systemuse,USER_NAME() as Username, suser_sname() as suser, SESSION_USER as session

Questions about posting. See faq183-874

 

[vongrunt]

Longshot: maybe you have session_user or user as column default...

 

[kaijanm]

Thanks for the input. I will play with it tomorrow. I ran out of time today.

Thanks!
Kimberly