Enable advanced features — Direct SQL, stored procedures, and the audit app
Three pre-built apps. None grant access by default. What each is for and how to give the right users in.
By the end of this lesson
- Direct SQL available to your power users
- Stored procedures available where supported (SQL Server, PostgreSQL)
- Audit app surfaced to the admins who should see it
You'll need
- Admin role in DashboardFox
- Allow Direct SQL and/or Allow Stored Procedures already enabled on the data source (see Additional data source settings)
Background
Every fresh DashboardFox instance ships with three pre-built apps, visible at Settings → Integrations → Active Integrations. They're already wired up — but no user has access to them by default, and that's on purpose.
DashboardFox audit — a pre-populated semantic layer over the system's own audit data. Who's logging in, what reports are running, what changes are being made. Useful for admin and compliance teams; usually not for end users.
Advanced SQL — surfaces raw SQL writing for users who can do it. A Composer with access here can write SELECT statements directly against any data source where Allow Direct SQL is enabled.
Advanced Reports — surfaces stored procedures as runnable reports. A Composer with access here can pick a stored procedure on any SQL Server or PostgreSQL data source where Allow Stored Procedures is enabled, set parameters, and run it.
The pairing model. For Advanced SQL and Advanced Reports, capability lives in two places — DBMS-side (the Allow flags in Additional data source settings) and user-side (the per-user role grant on the pre-built app). Both halves required. Turn on the flag without granting access and nothing changes; grant access without the flag and the user sees the app but no data sources are listed inside it.
Stuck on what to grant whom? Email team@dashboardfox.com with what your users need to do. Same business day.
Do it
-
Open Settings → Security → Apps — the bulk-grant view
You can grant the same access from two places: Settings → Security → Users → [user] → App Assignments (one user at a time) or Settings → Security → Apps → [app] → Edit (everyone for that app at once). For more than two or three users, the second is faster.
Open Settings → Security → Apps. You'll see all your apps listed, including the three pre-built ones. Click Edit on the app you want to grant — start with Advanced SQL if you're following the example.
-
Grant Composer to your power users (and yourself)
The edit panel shows every user in your instance. For each user who should write raw SQL, set their role on this app to Composer. Click Save & Apply.
That's the user-side grant. The user can now go to the Composer → Advanced SQL view and see a list of every data source they have access to that also has Allow Direct SQL turned on. Both halves of the switch.
Repeat the same flow for Advanced Reports (stored procedures) and DashboardFox audit, granting whichever role makes sense for each user. Composer = can build/run; Agent = view-only.
-
Set up downstream agents — the 3-app cascade
This is the part that catches every admin once. When a Composer builds a Direct SQL report or runs a stored procedure and shares the resulting saved report with view-only users, those Agent users need three grants to actually see it:
- Agent on the pre-built app (Advanced SQL or Advanced Reports) — so they can see reports of that type.
- Agent on the underlying data source app — so they have permission to read the data being queried.
- Library folder permission on whichever folder the report was saved in — so they can find the report in the Library.
Miss any of the three and the report doesn't appear. The first two are app grants done in Settings → Security → Apps; the third is set on the folder itself in the Library. The Roles & permissions lesson covers the cascade in depth.
-
Verify in the Composer (and log out / back in if needed)
Sign in as the user you just granted (or have them log in). Open the Composer. The pre-built app(s) you granted should appear in their app list. Click Advanced SQL — they should see the data sources where Allow Direct SQL is enabled. Click Advanced Reports — they should see the data sources with Allow Stored Procedures and the procedures available to run.
If a user reports that the new app isn't showing up, the most common fix is a fresh sign-in. The session caches permissions; signing out and back in picks up the updated grants immediately.
You have a working connection. Below is what to tighten before real users see it. Skip if you're just testing.
Make it real
Composer vs Agent for Direct SQL — restrict who can write SQL
Anyone with Composer on Advanced SQL can write arbitrary SELECT statements against any data source where Direct SQL is allowed. That's a real privilege — they can hit large tables, write inefficient joins, query columns the semantic layer would have hidden. Keep the Composer list short. The view-only Agents can run what those Composers save without being able to write new queries themselves.
If your team's only Direct SQL use case is "we have one analyst who occasionally writes raw SQL," grant Composer to that one person and Agent to anyone who consumes their reports. Don't bulk-grant Composer.
Lock down stored-procedure surface area at the database level
Allow Stored Procedures exposes every stored procedure the connecting DBMS user has EXECUTE permission on. The cleanest control is at the database — restrict the DashboardFox connection user's EXECUTE grants to only the procedures you want surfaced. Anything they can't execute won't appear in DashboardFox, regardless of who has access on the user side.
For SQL Server: GRANT EXECUTE ON dbo.proc_name TO <dbf_user> on the procedures you want, REVOKE EXECUTE ON SCHEMA::dbo FROM <dbf_user> as the catch-all blocker. For Postgres: GRANT EXECUTE ON FUNCTION <name>(<args>) TO <dbf_user>, restricted to specific functions.
Use groups for scale, not per-user grants
Granting the audit app or Advanced SQL one user at a time across 50 agents is busywork. Create a group at Settings → Security → Groups (e.g., SQL Power Users, Audit Reviewers), put the relevant users in the group, then grant the group the role on the app. Adding new users later is one click, not three pages of scrolling. Group-based grants are also easier to audit.
Plan audit-app access deliberately
The DashboardFox audit app surfaces sensitive data — who logged in when, what reports they ran, what they exported. Don't grant it broadly. Typical access pattern: Admins get Composer (so they can build new audit reports), the audit/compliance lead gets Composer or Agent depending on their needs, everyone else gets nothing. If you set up scheduled audit-summary deliveries, let the schedule do the broader visibility — not direct app access.
If you're stuck
The classic stumbles, in roughly the order they come up.
I granted Advanced SQL to a user but they don't see any data sources inside it
Half of the switch is missing. The Allow Direct SQL flag isn't enabled on any data source the user has access to. Open Settings → Integrations → Active Integrations, edit the relevant data source, go to Additional Settings, tick Allow Direct SQL, save. The data source then appears in the user's Advanced SQL view. See the Additional data source settings lesson.
Stored procedures menu is empty even though I enabled it
Two possibilities. (1) No procedures exist on that database. (2) The DBMS user DashboardFox is connecting with doesn't have EXECUTE permission on the procedures. Test (2) by signing into the database directly with that user and trying to run one. If it fails, the fix is database-side: GRANT EXECUTE on the right procedures to that user.
I shared an Advanced SQL report and my agent users can't see it
Classic 3-app cascade miss. Agents need Agent on Advanced SQL, Agent on the underlying data source app, AND folder permission on where the report was saved. Open Settings → Security → Apps and check both apps for the user; open the Library folder permissions for the third. Roles & permissions covers it.
I granted the role but Composer still shows the old app list
The session is caching permissions. Have the user sign out and back in. The new grants pick up immediately on a fresh session.
My Composer can run a report but exporting doesn't work
Export is governed separately at the data source level (and at the Library report level). If a user can run a report but the export button is missing or fails, it's not an Advanced SQL or Advanced Reports issue — check the data source's settings and the report's own export configuration. If you can't find the lever, email us.
None of these match my situation
Email team@dashboardfox.com with the user, the app, what you've granted, and what they're seeing. Real human, same business day.
