Troubleshooting Native Database Queries and the “Permission Denied” error.

Running native SQL queries inside Power Query, either by writing SQL into the database connector dialog or using Value.NativeQuery in M-code, frequently triggers a “Permission Denied” error. This error occurs because Power Query requires explicit user approval to run custom database scripts, or because the database user lacks server-side execution rights.

Quick Answer:

Open Excel and navigate to Data > Get Data > Query Options. Under Global, select Security, and uncheck Require user approval for new native database queries. Click OK. If the error continues, your database user account lacks server-level SELECT or VIEW DEFINITION permissions on the target database objects.

---

Two Distinct Sources of “Permission Denied”

When dealing with this error, determine whether the failure originates inside the Excel client or on the database server:

[Native SQL Query Entered in Power Query]
                   │
                   ▼
       Is Client Approval Required?
      /                            \
   (YES)                           (NO)
    │                               │
Power Query Security Block          Database Server Evaluation
(Excel Dialog / Client Halt)        (Server-side User Rights)
                                    │
                                    ├── User lacks SELECT/EXECUTE ──► "Permission Denied" (DB Engine)
                                    └── User authorized ────────────► Data Returned

• Client-Side Blocker: Excel blocks the query before sending it over the network because native queries could contain malicious commands (such as DROP TABLE or data manipulation calls).
• Server-Side Blocker: Excel sends the query, but the database engine (such as SQL Server or Oracle) rejects it because the database user credentials lack permission to read the requested tables, views, or stored procedures.

---

Step 1: Disable the Native Query Approval Prompt in Excel

If the error appears directly in the Power Query editor when adding a step or refreshing a query, adjust the client approval settings:

1. Open Excel and click the Data tab.
2. Select Get Data > Query Options.
3. In the left navigation pane, under Global, select Security.
4. In the main panel, locate Native Database Queries.
5. Uncheck the box marked Require user approval for new native database queries.
6. Click OK.

Return to the Power Query editor and click Refresh Preview. If the issue was caused by client-side protection, the query will now run.

For broader administrative details on managing these script prompts across scheduled tasks, see Handling “Native Query” security warnings that block automation.

---

Step 2: Validate Server-Side Database Permissions

If disabling the client setting does not resolve the error, the database engine itself is rejecting the query.

Confirm Object-Level Permissions

To run a native query, your database credentials must have read access to every table and view referenced in your SQL statement:

• For standard queries: The database user requires SELECT permissions on all referenced tables and schemas.
• For stored procedures: The user requires EXECUTE permissions on the procedure.
• For system views: Certain metadata queries require VIEW DEFINITION or elevated system catalog rights.

Test with a Minimal Query

To confirm whether the issue is object-specific, test the connection using a basic query in the Power Query formula bar:

= Sql.Database("YourServerName", "YourDatabaseName", [Query="SELECT 1 as TestColumn"])

• If SELECT 1 succeeds, your database connection and credentials are valid, confirming that your user lacks permissions on the specific tables in your original query.
• If SELECT 1 fails with “Permission Denied,” your login is either mapped to an unauthorized database role or connecting with the wrong authentication type. Read Troubleshooting “Login failed for user”: Windows vs. SQL Authentication to resolve database account mapping.

---

Step 3: Clear and Re-authenticate Data Source Credentials

Stored credentials that are outdated or tied to an unprivileged Windows account will trigger persistent permission errors:

1. In Excel, go to Data > Get Data > Data Source Settings.
2. Select Data sources in current workbook.
3. Highlight the target database and click Clear Permissions.
4. Return to your worksheet and click Refresh All.
5. When prompted for credentials, choose the exact authentication method configured for your server (e.g., Database for SQL Server logins or Windows for domain-authenticated users).
6. Enter valid credentials and set the privacy level appropriately.

For step-by-step guidance on removing corrupted connection tokens, review Managing Data Source Settings: How to clear and reset Global Permissions.

---

Step 4: Address the Formula Firewall Collision

If your native query incorporates parameters from another query or local cell (such as filtering by a date entered in an Excel sheet), Power Query’s firewall may misidentify the dynamic query string as a security violation:

// Risky pattern: passing local cell variables directly into a native query
let
    LocalVal = Excel.CurrentWorkbook(){[Name="SelectedRegion"]}[Content]{0}[Column1],
    Source = Sql.Database("Server01", "SalesDB", [Query="SELECT * FROM Orders WHERE Region = '" & LocalVal & "'"])
in
    Source

The Mashup Engine blocks this operation by default to prevent injection attacks and data leakage. To resolve this:

• Enable Fast Combine as outlined in How to configure “Fast Combine” to bypass security overhead.
• Alternatively, structure your queries using standard staging tables as detailed in Combining Local Excel Files with SQL Server: The Firewall workaround.