Sql-Server

為什麼 SESSION_USER 返回 dbo 而不是 SQL 登錄？

  • January 11, 2017

我在 GP 表上有一個觸發器，它記錄關鍵欄位何時更改以及誰更改了它。下面是觸發器範例。

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

在 99% 的情況下，這會返回更改欄位的使用者的正確名稱。如果任何 ERP 人員更改了一個欄位（包括我自己），則使用者會顯示一個 dbo。我一直認為這是一個訪問問題。但是，我們發現一些使用者也以 dbo 而不是他們的名字返回。

在此處輸入圖像描述

你對我應該從哪裡開始尋找有什麼建議嗎？

您將使用者與登錄混淆。使用者是數據庫級別的“主體”，而登錄是伺服器級別（也稱為實例級別）的主體。

每個數據庫都有一個dbo映射到登錄名的使用者。*當“目前”數據庫是有問題的數據庫時，*將dbo顯示映射到使用者的登錄。dbo``SESSION_USER

並且，sysadmin固定伺服器角色中的登錄名（包括通過作為固定伺服器角色成員的 Windows 組連接的 Windows 登錄名sysadmin）將顯示dbo為SESSION_USER. 即使登錄名映射到該數據庫中具有除預設模式以外的使用者dbo（因為此問題與模式無關），也會出現這種情況。下面的測試程式碼中的“測試 3”中顯示了一個範例。

SESSION_USER 當數據庫中沒有要映射的使用者時，可以返回登錄名。例如，如果登錄名具有CONTROL SERVER伺服器權限，以便他們可以連接到任何數據庫但不屬於sysadmin固定伺服器角色，SESSION_USER則將僅返回那些沒有使用者映射的數據庫的登錄名。下面的測試程式碼中的“測試 5”中顯示了一個範例。

您要使用的是ORIGINAL_LOGIN(). 此函式將返回用於連接實例的實際登錄名，即使已使用模擬將目前安全上下文更改為另一個登錄名的安全上下文。

以下測試說明並證明了上述行為：

設置

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

測試

--------------------------------------------------
-- 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;

清理

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

USE [master];
DROP DATABASE [GazooDB];

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

**！！！此外，**在 SQL Server 中使用觸發器時，在執行以下操作時需要非常小心：

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

問題是INSERTED偽表中可能有多行，具體取決於受 DML 操作影響的行數。如果更新的行不止一行，則這兩個變數中的值將是最後一行的值INSERTED（並且它們沒有特定的順序）。如果列的值在所有行中都相同，那麼我認為這並不重要，但最好不要以這種方式構造觸發器程式碼。

引用自：https://dba.stackexchange.com/questions/160550

相關問答

Sql-Server

現有連接是否會立即兌現對 TRUSTWORTHY 設置的更改？

  • December 22, 2021
檢視問答
Sql-Server

為安全域防火牆後面的 sql server 打開哪些埠

  • August 27, 2022
檢視問答
Sql-Server

觸發分區表和性能

  • July 7, 2022
檢視問答
Sql-Server

幫助創建觸發器

  • June 17, 2022
檢視問答
Sql-Server

勒索軟體能否將自身嵌入到 SQL 備份文件中

  • May 13, 2022
檢視問答
Sql-Server

SET IDENTITY_INSERT 的權限是否低於 db_ddladmin？

  • April 3, 2022
檢視問答