sql-server - 如果用户是用户分配的托管标识，则确定 Azure SQL Server 中的用户名

Question

我使用从 Web 应用程序分配的用户托管标识连接 Azure SQL Server。到目前为止，这工作得很好，我们的日志记录过程将在数据库中记录该应用程序(和其他应用程序)的所有事件。

要确定当前代理用户的用户名，我通常可以在任何存储过程中使用类似

SELECT @ORIGINAL_LOGIN()

这将返回连接用户的用户名。

在我的例子中，托管身份的用户名是“octservice”。但是，当“octservice”连接并且我记录其事件时，@ORIGINAL_LOGIN() 函数不会返回其用户名，而是从 Azure AD 返回类似 ClientID@TenantID 的内容。

我怎样才能确定用户名，就像 SSMS 中显示的那样？ClientID@TenantID 不是该用户的 USER_ID。

Answer 1

当我想在应用 EFcore 迁移后将当前用户从 db_owner 角色中删除时，我遇到了同样的问题。

我的解决方案是首先使用正则表达式检查 SELECT SUSER_NAME() AS Value 返回的值是否是 Azure 身份的名称:

(?im)^[0-9A-F]{8}-([0-9A-F]{4}-?){3}[0-9A-F]{12}@[0-9A-F]{8}-([0-9A-F]{4}-?){3}[0-9A-F]{12}$

然后从表sys.database_principals中查找用户名:

SELECT name AS Value from sys.database_principals WHERE sid = SUSER_SID()

使用该名称，我能够创建 SQL 语句来删除角色成员:

ALTER ROLE [db_owner] DROP MEMBER [{Value}]