1

New to SQL Managed Instances. Currently I am testing our on-prem SQL databases in an Azure Managed Instance. There are some logging procedures in our application database which are gathering information to save in an audit trail. Some of these are referencing some of the DMVs such as sys.dm_exec_connections and sys.dm_exec_sql_text.

Accessing these using the MI administrator user works fine. However it is the AD service account which will be accessing the database. This service account does not appear to have access to those.

The service account has been added to the db_owner role within the database but I am not able to set at the server or database level the view server state and view all definitions permissions. I cannot see anything which could be the equivalent in a Managed Instance.

Does anyone know how and where to look in order to resolve this?

I have even tried with an account that is the AD Admin account of the instance.

Many thanks.

Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
C Gall
  • 11
  • 1

1 Answers1

0

Please use sql logins instead of AAD login/users for this scenario.

In the current version of Managed Instance you can either create global AAD Admin that don't have full server admin privileges, or you can create contained AAD users as CREATE USER myusr FROM EXTERNAL_PROVIDER that cannot get server-level permissions because grant permission at server level can be given to server-level logins and not database-level users.

In the future we will have better AAD logins that are much similar to the existing Windows logins, but in the meantime, it might be painful if you try to setup this scenario with the existing AAD login/users.

Jovan MSFT
  • 13,232
  • 4
  • 40
  • 55