I connect Azure SQL Server using a user assigned managed identity from a webapp. This works fine so fare, and our logging process shall log all activities of this app (and others) in the database.
To determine the username of the current acting user, i can usuallly use in any stored procedure something like
SELECT @ORIGINAL_LOGIN()which will return the username of the connected user.
The username of the managed identity is in my case "octservice". But when "octservice" connects, and i log its activities, the @ORIGINAL_LOGIN() Function does not returns its username, it returns something like ClientID@TenantID from the Azure AD.
How can i anyway determine the username, like it appears in SSMS ? The ClientID@TenantID is not the USER_ID of this user.
2条答案
按热度按时间6g8kf2rb1#
I had the same problem when I wanted to drop the current user from the
db_ownerrole after applying EFcore-migrations.My Solution is to first check whether the value returned by
SELECT SUSER_NAME() AS Valueis the name of an azure identity using a regex:then looking up the username from the table
sys.database_principals:With that name, I was able to create SQL statement to drop the role member:
mwecs4sa2#
Try
SELECT SYSTEM_USER()? It's a synonym forSUSER_SNAME(). I've seen it referenced once or twice with regard to managed identity.