3

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.

enter image description here

Do you have any suggestions as to where I should start looking?

  • ,Because 'dbo' is the default schema in SQL Server. – Md Haidar Ali Khan Jan 10 '17 at 12:27
  • but then why do some show the name and some don't when everyone logs in with a SQL authentication login. could it be the ODBC they connect with or the account permissions. – Jay Jan 10 '17 at 12:33
  • ,When database objects are referenced by using a one-part name, SQL Server first looks in the user's default schema. If the object is not found there, SQL Server looks next in the dbo schema. If the object is not in the dbo schema, an error is returned. – Md Haidar Ali Khan Jan 10 '17 at 12:43
  • 1
    @MdHaidarAliKhan and Jay: the value returned by SESSION_USER has nothing to do with Schemas. Please see my answer for details and example code proving this point :-). – Solomon Rutzky Jan 10 '17 at 18:12
4

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.

  • 1
    Thank you, this helped. we found that myself and the other ERP users were given sysadmin (cloned accounts) – Jay Jan 11 '17 at 7:08
3

SESSION_USER is a synonym to USER_NAME(). Which the USER_NAME function by definition

USER_NAME to obtain the database user name associated with a database user ID

If the user making the update to that database table does not have a user in the database it will default to the "dbo" schema. This is shown below: enter image description here

My login does not have an associated user account in the AdventureWorks2014 database. However, if I execute the same thing as a user that is in the database I will get this: enter image description here

You will most commonly see this if you do not create a user in the database for every person that can modify data. A better option is to change the trigger to capture SUSER_NAME so you obtain the login that made the change, not just the user. I have seen cases where the user was mapped to a different login name so your auditing records can be flawed if that is not managed properly.

0

As per MSDN BOL The dbo schema is the default schema for a newly created database. The dbo schema is owned by the dbo user account. By default, users created with the CREATE USER Transact-SQL command have dbo as their default schema.

For Your Ref :- Ownership and User-Schema Separation in SQL Server

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.