Why does SESSION_USER return dbo instead of SQL login? I have a trigger on a GP table that records when a critical field is changed and who changed it. A sample of the trigger follows.

CREATE TRIGGER [dbo].[TRACKAPP_UPDATE] ON [dbo].[Table]
AFTER UPDATE
AS
DECLARE @MDFUSRID   char(15)
DECLARE @SOPNUMBE   char(32)
DECLARE @DOCSTATUS  INT

BEGIN
BEGIN
IF UPDATE(DOCSTATUS)
BEGIN
    SELECT @MDFUSRID = SESSION_USER
    SELECT @SOPNUMBE = SOPNUMBE FROM inserted
    SELECT @DOCSTATUS = DOCSTATUS FROM INSERTED


In 99% of the cases this returns the correct name of the user who changed the field. If any of the ERP people change a field (myself included) the user shows a dbo. I have always assumed this is an access issue. However, we have found a couple of users who also return as dbo rather than their name. 



Do you have any suggestions as to where I should start looking?
You are confusing Users with Logins. Users are "principals" at the Database-level while Logins are principals at the Server-level (a.k.a. Instance-level).

Each Database has a single User, dbo, that is mapped to a Login. A Login that is mapped to the dbo User will show up as dbo for SESSION_USER when the "current" Database is the DB in question.

AND, Logins in the sysadmin fixed server role (including Windows Logins that connect via a Windows Group that is member of the sysadmin fixed server role) will show up as dbo in SESSION_USER. This will be the case even if the Login is mapped to a User in that Database that has a default Schema other than dbo (since this issue has nothing to do with Schemas). An example of this is shown in "Test 3" in the test code below.

SESSION_USER can return a Login name when there is no User in the DB to map to. For example, if a Login has the CONTROL SERVER server permission such that they can connect to any DB but are not in the sysadmin fixed server role, SESSION_USER will return the Login name for only those DBs that do not have a User mapping. An example of this is shown in "Test 5" in the test code below.

What you want to use is ORIGINAL_LOGIN(). This function will return the actual Login that was used to connect to the instance, even if Impersonation has been used to alter the current security context to that of another Login.

The following tests illustrate and prove the behavior noted above:

SETUP

USE [master];
CREATE LOGIN [GazooLogin] WITH PASSWORD = 'NevrCrack';
CREATE DATABASE [GazooDB] COLLATE Latin1_General_100_CI_AS;
ALTER AUTHORIZATION ON DATABASE::[GazooDB] TO [sa];
GO

CREATE USER [GazooUser1]
  FROM LOGIN [GazooLogin]
  WITH DEFAULT_SCHEMA = [GazooSchema1];
GO

CREATE SCHEMA [GazooSchema1]
  AUTHORIZATION [GazooUser1];
GO


USE [GazooDB];
CREATE USER [GazooUser2]
  FROM LOGIN [GazooLogin]
  WITH DEFAULT_SCHEMA = [GazooSchema2];
GO

CREATE SCHEMA [GazooSchema2]
  AUTHORIZATION [GazooUser2];
GO


TESTS

--------------------------------------------------
-- Test 1:
USE [GazooDB];

EXECUTE AS LOGIN = N'GazooLogin';

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- database     CONNECT

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- GazooUser2   Dali\Solomon    GazooLogin  GazooLogin

USE [master];
SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- database     CONNECT

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- GazooUser1   Dali\Solomon    GazooLogin  GazooLogin


USE [GazooDB]; -- can only revert from DB where EXECUTE AS was run
REVERT;

--------------------------------------------------
-- Test 2:

USE [master];
EXEC sys.sp_addrolemember N'db_owner', N'GazooUser1';

EXECUTE AS LOGIN = 'GazooLogin';

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- lots of stuff :)

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- GazooUser1   Dali\Solomon    GazooLogin  GazooLogin

USE [GazooDB];

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- database     CONNECT

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- GazooUser2   Dali\Solomon    GazooLogin  GazooLogin

USE [master]; -- can only revert from DB where EXECUTE AS was run
REVERT;

--------------------------------------------------
-- Test 3:

EXEC sys.sp_droprolemember N'db_owner', N'GazooUser1';

EXEC sys.sp_addsrvrolemember N'GazooLogin', N'sysadmin';

EXECUTE AS LOGIN = 'GazooLogin';

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- lots of stuff :)

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- dbo  Dali\Solomon    GazooLogin  GazooLogin

USE [GazooDB];

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- lots of stuff :)

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- dbo  Dali\Solomon    GazooLogin  GazooLogin

USE [master]; -- can only revert from DB where EXECUTE AS was run
REVERT;

--------------------------------------------------
-- Test 4:

CREATE LOGIN [SuperDuperDatabaseOwner] WITH PASSWORD = 'NevaCrack';
ALTER AUTHORIZATION ON DATABASE::[GazooDB] TO [SuperDuperDatabaseOwner];

USE [GazooDB];

EXECUTE AS LOGIN = N'GazooLogin';

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- lots of stuff :)

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- dbo  Dali\Solomon    GazooLogin  GazooLogin

REVERT;

SELECT * FROM sys.database_principals;
SELECT * FROM sys.server_principals;


--------------------------------------------------
-- Test 5:

DROP SCHEMA [GazooSchema2];
DROP USER [GazooUser2];

USE [master];

EXEC sp_dropsrvrolemember N'GazooLogin', N'sysadmin';

GRANT CONTROL SERVER TO [GazooLogin];

EXECUTE AS LOGIN = N'GazooLogin';

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- GazooUser1   Dali\Solomon    GazooLogin  GazooLogin

USE [GazooDB];

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- lots of stuff :)

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- GazooLogin   Dali\Solomon    GazooLogin  GazooLogin

USE [master]; -- can only revert from DB where EXECUTE AS was run
REVERT;



--------------------------------------------------
-- Test 6:

USE [GazooDB];

EXECUTE AS LOGIN = N'SuperDuperDatabaseOwner';

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- dbo  Dali\Solomon    SuperDuperDatabaseOwner SuperDuperDatabaseOwner

REVERT;


CLEANUP

--------------------------------------------------
-- CLEAN UP:

USE [master];
DROP DATABASE [GazooDB];

DROP SCHEMA [GazooSchema1];
DROP USER [GazooUser1];
DROP LOGIN [GazooLogin];
DROP LOGIN [SuperDuperDatabaseOwner];




!!!  ALSO, when working with Triggers in SQL Server, you need to be very careful when doing things such as the following:

SELECT @SOPNUMBE = SOPNUMBE FROM inserted
SELECT @DOCSTATUS = DOCSTATUS FROM INSERTED


The problem is that the INSERTED pseudo-table can have multiple rows in it, based on how many were affected by the DML operation. If there is more than one row that was updated, the value in those two variables will be that of the last row in INSERTED (and they are not in a specific order). If a column's value is the same across all rows then I suppose it doesn't matter, but it is still a best practice to not structure Trigger code in this manner.