I have a c# desktop application that has the option to connect to a primary or secondary database, Primary is SQL Server 2022 Standard using AD integration, the Secondary is Azure DB S2 using MS EntraID Interactive, using our “local” AD sync'd to EntraID. I use SQL Compare and SQL Data Compare to make sure they are identical for testing, and they are identical for this question. Problem I'm having is that when I switch between SQL Server and Azure DB, I'm getting a SELECT permissions error in Azure DB to view a table while running a stored proc. Same proc on SQL Server, no error. I was always told the if all users are using Stored Procs, then select permissions on tables directly is not necessary, and removing them follows least privilege. But they seem to be necessary in Azure DB. Can anyone confirm this? Why the change? Am I missing something? Oddly, the stored proc also uses DELETE and UPDATE on tables which have not generated permission errors.
I have a c# desktop application that has the option to connect to a primary or secondary database, Primary is SQL Server 2022 Standard using AD integration, the Secondary is Azure DB S2 using MS EntraID Interactive, using our “local” AD sync'd to EntraID. I use SQL Compare and SQL Data Compare to make sure they are identical for testing, and they are identical for this question. Problem I'm having is that when I switch between SQL Server and Azure DB, I'm getting a SELECT permissions error in Azure DB to view a table while running a stored proc. Same proc on SQL Server, no error. I was always told the if all users are using Stored Procs, then select permissions on tables directly is not necessary, and removing them follows least privilege. But they seem to be necessary in Azure DB. Can anyone confirm this? Why the change? Am I missing something? Oddly, the stored proc also uses DELETE and UPDATE on tables which have not generated permission errors.