Is End User authorization required for SQL queries? Can Creator authorization bypass End User authorization?

End-user authorization is required when the connector or app is designed to run SQL queries under the end user’s identity. Creator (or author) permissions do not automatically bypass this, and cannot be used as a “proxy” unless the solution is explicitly redesigned to run under an app/service identity instead of the end user.

1. Why the end user sees an authorization prompt

Across Azure SQL, Synapse, and Fabric SQL engines, authorization is always evaluated against the identity that connects to the database:

• Authorization is controlled by database role memberships and object-level permissions assigned to that identity.
• Examples of roles/permissions commonly used for data access:
  • db_datareader – read data
  • db_datawriter – write data
  • db_owner – full control (generally too powerful for end users)
• Best practice is to grant each identity the least privileges necessary.

When a connector is configured to use the signed-in user’s identity (for example, via Microsoft Entra ID), the platform passes that user’s credentials to the SQL engine for both authentication and authorization. If additional consent or authorization is required, the user is prompted, and the database enforces that user’s permissions only.

This is by design: the SQL engine does not “inherit” the creator’s or admin’s permissions when the end user is the one connecting.

2. Can creator authorization bypass end-user authorization?

No, not in the sense of “use the creator’s rights while the end user is the one being authenticated.”

Key points from the platform behavior:

• Authorization is tied to the connecting identity’s roles and permissions, not to who created the topic/flow/app.
• A creator or admin can:
  • Configure roles and permissions in the database (for example, grant db_datareader to a user or a role).
  • Configure which identity the connector uses (user identity vs. app/service identity).
• A creator or admin cannot:
  • Silently run SQL queries under their own permissions while the connector is authenticating as the end user.
  • Override the end user’s consent prompts when the model is “run as user,” unless the platform explicitly supports admin consent for the app itself (for example, in some app-authorization flows like Databricks apps, where admins can consent on behalf of users, but the app still runs under the user’s scopes).

So creator authorization does not “bypass” end-user authorization. Instead, the creator can choose a different execution identity for the connector.

3. How to avoid per–end-user prompts: run with an app/service identity

To avoid end-user authorization prompts and per-user SQL permissions, the solution must be designed so that SQL access is performed under a non-user identity (for example, a service principal or dedicated application account). In that model:

1. Configure the SQL engine to trust and authorize the app identity:
   • Create a login/user for the app/service principal or SQL login.
   • Grant appropriate roles/permissions, such as:
     • db_datareader (and db_datawriter if writes are needed).
     • Additional object-level permissions as required.
   • Follow least-privilege: avoid db_owner unless absolutely necessary.
2. Configure the connector to authenticate as the app identity instead of the end user:
   • For Microsoft Entra–based authentication, use a service principal and pass an access token for app-based authentication.
   • For SQL authentication, configure a SQL username/password dedicated to the app.
3. Resulting behavior:
   • The SQL engine authorizes based on the app identity’s roles/permissions.
   • End users no longer need to grant SQL-level permissions individually, because they are not the connecting identity.
   • This is not a bypass of authorization; it is a different authorization model (centralized app identity vs. per-user identity).

This pattern is reflected in the Spark connector guidance, where the identity can be a user or an app, and the required permissions (db_datareader, db_datawriter, etc.) must be granted to that identity.

4. Security and governance considerations

When switching from “run as user” to “run as app/creator identity,” there are important implications:

• Loss of per-user data isolation: all queries run with the app’s permissions, so row-level or object-level restrictions based on the end user’s identity will not apply unless implemented separately (for example, via Row-Level Security policies that use other context).
• Strong need for least privilege on the app identity: grant only the minimum tables/views and operations required.
• Auditing: audit logs will show the app/service identity as the actor at the SQL layer, not individual end users.

5. Summary

• End-user authorization prompts are required when the connector is designed to use the end user’s identity.
• Creator/admin permissions cannot be used to transparently bypass those prompts while still running as the end user.
• To avoid per-user prompts, configure the connector to use an app/service identity with appropriate SQL roles/permissions; then the database authorizes that identity instead of the end user.
• Always apply least-privilege principles and consider the impact on data isolation and auditing.

References:

• Authorize database access to SQL Database, SQL Managed Instance, and Azure Synapse Analytics
• Secure a dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics
• Spark connector for SQL databases (Preview)
• Authorization in SQL database in Microsoft Fabric
• SQL granular permissions in Microsoft Fabric
• Configure authorization in a Databricks app