search

amachanic / sp_whoisactive

sp_whoisactive
GNU General Public License v3.0
1.17k stars 289 forks source link

Login_name not useable in Azure SQL DB for Managed Identities #55

Closed ryandevries closed 3 years ago

ryandevries commented 3 years ago

For connections made from other MS services using managed identities, the login_name is two guids separated by an @. The first is the guid of the application ID , and the second is the guid of the tenant ID. Would be cool to pull the name of the managed identity instead. I'm not sure how to get the tenant id, but to get the application ID from converting the sid from sys.database_principals:

DECLARE @tenantID uniqueidentifier = '<guid>' SELECT Name, LOWER(CONCAT(CAST([sid] AS uniqueidentifier), '@', @tenantID)) AS GUID FROM sys.database_principals WHERE type = 'E'

erikdarlingdata commented 3 years ago

@ryandevries we haven't heard back from you in a while about working on this, so I'm going to close it out for now. if you decide you want to work on it in the future, let us know and we'll re-open it for you. Thanks!