xp_logininfo alternative for Azure SQLDBMI with AzureAD
How can I request login info about Azure AD ( synced) authorities on my Azure SQLDB Managed Instance ?
EXEC master..xp_logininfo @acctname = 'mydomain\theAccount', @option = 'all'
We need to figure this out from within the SQLInstance and not as a reaction on the Azure Portal or Powershell AD info.
Can you please provide more feedback, as Dimitri asked in a comment below:
Giuseppe Guida commented
I also would like to know to which AD Group belongs a login in Azure Datawarehouse. So that I can map that to trow level security!
Not sure of Johan's use-case, but in my use case, the actual logons are members of a group in Active Directory, and it is that Group which we have configured as a SQL principal. With that, when I try to look-up additional information about the user, we won't find them in the sys.server_principals DMV under their name.
I'm sorry. I must have missed the reply by Dimitri.
xp_logininfo shows all paths with which a user can enter your instance or database.
(A)AD group-membership will not come up using the mentioned DMV.
Dimitri Furman commented
Thanks for bringing this up. Could you please elaborate on what you are trying to achieve in more detail? Would the information provided by sys.server_principals and sys.server_permissions DMVs be sufficient, and if not, why?