Azure Sql Managed Instance - Service Principal access issue

Question

We have configured Sql Managed Instance and currently configured MS Entra MFA but we can't use for application login/Service account which is prompting for MFA so we have created Service Principal and provided access after adding into Managed Sql Server Instance Database.

I am able to connect to the Instance/Database and can run SELECT but can't run DML statement (Insert/Update/Delete).

I have provided Read and Write access also, evne thogu I have added DB_Owner role permission but still getting error:

The UPDATE permission was denied on the object 'order', database 'Ordertest', schema 'dbo'

Appreciate your feedback!

Thank you in advance!

Answer 1

Hi @pdsqsql

Thanks for the Question and using Microsoft Q&A

As per my understanding, your facing issues in DML operations in Azure SQL managed instance.

Granting DML (Data Manipulation Language) permissions in Azure SQL Managed Instance involves providing users or roles with the necessary permissions to perform operations like INSERT, UPDATE, DELETE, and SELECT on database tables. Here’s a step-by-step guide to help you through the process:

Step-by-Step Guide

1. Connect to Azure SQL Managed Instance: Use SQL Server Management Studio (SSMS) or any other SQL client to connect to your Azure SQL Managed Instance.
2. Select the Database: Ensure you are connected to the correct database where you want to grant permissions.
3. Grant Permissions: Use the GRANT statement to provide the necessary DML permissions. Here are some examples: Grant SELECT Permission: GRANT SELECT ON [schema_name].[table_name] TO [user_or_role]; Grant INSERT Permission: GRANT INSERT ON [schema_name].[table_name] TO [user_or_role]; Grant UPDATE Permission: GRANT UPDATE ON [schema_name].[table_name] TO [user_or_role]; Grant DELETE Permission: GRANT DELETE ON [schema_name].[table_name] TO [user_or_role];

Example

Suppose you have a table named Employees in the HR schema, and you want to grant SELECT and INSERT permissions to a user named xyz. You would execute the following commands:

GRANT SELECT ON HR.Employees TO xyz;

GRANT INSERT ON HR.Employees TO

xyz

Hope this helps. Do let us know if you have any further queries

  

 

 

Answer 2

Apparently, you have granted permissions to another principal than the one you think you did. Or you granted permissions in the wrong database.

Have the application to run

SELECT SYSTEM_USER, * FROM sys.user_token

to see what comes back.