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],
S

StackExchange Database Administrators Q#160550, answer score: 10