Impersonating a server principal (login) in Azure SQL MI

  • feersum_endjinn

    SSCommitted

    Points: 1966

    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):

    Ssms_P4Y1rTrmGy

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

    Ssms_S3IIu6TN3I

    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: 80379

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

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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply