Impersonating a server principal (login) in Azure SQL MI

• feersum_endjinn

  SSCommitted

  Points: 1966

  More actions

  December 3, 2019 at 5:27 pm

  #3703909

  Hi all,

  I seem to be doing something wrong or trying to do something that's not supported. I frequently use EXEC AS LOGIN = 'DOMAIN\USERNAME' to test permissions and access on on-premises SQL instances (usually combined with sys.fn_mypermissions).

  However this doesn't seem to work on Azure SQL Managed Instances (version at date of this post: Microsoft SQL Azure (RTM) - 12.0.2000.8 Oct 12 2019 22:46:48 Copyright (C) 2019 Microsoft Corporation) for Azure AD users if access to the user is granted via an Azure AD security group (i.e. almost all users in my case!). If the user has been added as a login directly then impersonation works. I am a sysadmin on both instances.

  Does anyone know how to impersonate a login that is a member of an Azure AD group?

  For example, this is on an on-premises server (user has access via AD group):

  

  And this is on a Azure SQL MI (AAD user has access via AAD security group):

  

  Full error message is:

  failed

  Cannot execute as the server principal because the principal "rachel.mak@nelcsu.nhs.uk" does not exist, this type of principal cannot be impersonated, or you do not have permission.

• Site Owners

  SSC Guru

  Points: 80320

  More actions

  December 4, 2019 at 6:10 pm

  #3704428

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.