How can we help you today? How can we help you today?

AreSQL Server permissions different than Azure DB?

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.

Dan Guzman
1

Comments

2 comments

Sort by
  • CathyWilliamson

    @repo: If the stored procedure uses dynamic SQL (e.g., EXEC or sp_executesql), the ownership chain is broken, and explicit permissions are required on the objects referenced in the dynamic SQL 

    CathyWilliamson
    0
  • graded

    Thanks for pointing that out, Cathy. That makes sense. If dynamic SQL is involved, then the normal ownership chaining rules wouldn’t apply, and you’d need explicit permissions on the referenced objects. I’m running into a similar issue on Azure SQL DB where a stored proc works fine on SQL Server but throws a SELECT permission error on Azure. I’ll double-check whether there’s any dynamic SQL in the proc that's causing the break in the chain. Appreciate the insight!

     

     

    graded
    0

Add comment

Please sign in to leave a comment.