0

I recently created an Azure Sql database instance into which I deployed my EF Core data model. The database has a system-assigned managed identity assigned to it which is member of the db_datareader and db_datawriter built-in roles. I should be able to read/write from/to any table or view.

Still I am getting the below error when trying to insert data into it. The table is not in the dbo schema but has a seperate one.

ExType= Microsoft.Data.SqlClient.SqlException; Ex= Cannot find the object "<TABLE_NAME>" because it does not exist or you do not have permissions.; InnerEx=

It should work, right? Do you have any idea what I'm doing wrong? Any help is greatly appreciated;

baouss
  • 1,312
  • 1
  • 22
  • 52
  • You didn't post the full exception or any EF Core-related code. Did you specify the correct schema in the DbContext or class configuration? – Panagiotis Kanavos Jun 03 '22 at 08:12
  • Hi, that's the whole exception message provided above. There was no inner exception message returned by EF Core. I can confirm it working when I run the code locally in VS Code, using my own identity rather than the system-assigned identity from Azure. This shows: The code is not the problem (the database and the DbContext do match, it's code-first deployment, for got to say sorry), permissions are. However, as I laid out in the question the function apps identity is already member of the datareader/datawriter roles which should give it a blanque cheque with regards to permissions. – baouss Jun 03 '22 at 08:37

2 Answers2

0

As per the Microsoft documentation these are the fixed database roles.

As you mentioned the built-in roles, db_datareader and db_datawriter.

Try with the db_owner role as it will be allow you to do all the actions within that database and hopefully you will not get this error.

Make sure to recheck the connection string name in web.config file and the dbcontext base constructor parameter name are same.

Check all access rights for specific user in Azure SQL Database like this.

RajkumarPalnati
  • 541
  • 2
  • 6
0

Solved now. It turns out the error message was a bit misleading. As part of the code's logic, a stored procedure was called which needed the ALTER permission on that table. After that records are inserted. After granting the alter permission on that table to the role the principal is assigned to, it works.

baouss
  • 1,312
  • 1
  • 22
  • 52
  • Glad that you have cleared the issue. Yes, we have to grant the appropriate permission on that specific table for that role. The above given link which has details about Grant. – RajkumarPalnati Jun 08 '22 at 13:00