You're definitely not alone—this is one of those subtle differences between on-prem SQL Server and Azure SQL Database that catches people off guard. In SQL Server 2022, if your stored procedure and the underlying tables are owned by the same user (like dbo ), you get the benefit of ownership chaining. That means users can run the stored proc without needing direct SELECT permissions on the tables—it just works. But in Azure SQL Database, especially when using Entra ID (formerly Azure AD) authentication and contained database users, that ownership chain can break down. Even if your schema and permissions are identical (as confirmed with SQL Compare/Data Compare), Azure SQL might still require explicit SELECT permissions on the table. What's odd in your case is that SELECT is failing, but DELETE and UPDATE are not. That makes me wonder:
Is the SELECT being done on a view, or maybe through dynamic SQL inside the proc? Both of those can bypass ownership chaining.
Are the proc and the tables definitely owned by the same principal (check with sys.objects or OBJECTPROPERTY() just to be sure)?
Is the proc running under the caller’s context, or using something like EXECUTE AS ?
You could try:
Adding EXECUTE AS OWNER to the proc definition
Explicitly granting SELECT on the table to the Entra ID user or role (just to confirm it resolves the error)
Double-checking that the login context in Azure really maps the same way as it does in SQL Server
In short: you're not wrong—your approach aligns with least privilege and standard best practices. Azure SQL just enforces things a bit differently due to how it handles identity and containment. Would love to hear if you find a clean workaround! Check Object ownership : SELECT o.name AS object_name, o.type_desc, s.name AS schema_name, u.name AS owner FROM sys.objects o JOIN sys.schemas s ON o.schema_id = s.schema_id JOIN sys.database_principals u ON o.principal_id = u.principal_id WHERE o.name IN ('YourProcName', 'YourTableName'); Try redefining the stored procedure with EXECUTE AS OWNER to enforce the proper context: CREATE OR ALTER PROCEDURE YourProcName
WITH EXECUTE AS OWNER
AS BEGIN
-- your logic here END To confirm it’s a permissions issue, you can temporarily grant SELECT directly: GRANT SELECT ON dbo.YourTableName TO [your_EntraID_user_or_role]; / comments
- Community
- General Forum
- AreSQL Server permissions different than Azure DB?
You're definitely not alone—this is one of those subtle differences between on-prem SQL Server and Azure SQL Database that catches people off guard.In SQL Server 2022, if your stored procedure and ...
0 votes
