web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Reporting in Excel - S...
Microsoft Dynamics NAV (Archived)

Reporting in Excel - SQL Permissions?

(0) ShareShare
ReportReport
Posted on by Ian Fletcher 2,445

Hello, i am struggling with trying to produce some reports in Excel to share with other users in the organisation. I can make an Excel report on my pc that uses MS Query & all is fine, if i save this on the network & then try to run this from a different pc i get a select error on the table's.

From investigation i believe it is because i have the SQL Role sysadmin, but the other Nav users just have the SQL Role Public - does this sound correct?

I have tried with a simple based on Opportunity & Opportunity Entry tables to look at Open Opportunities as i was hoping to create a pivot chart/Dashboard report for the senior management.

I know about Jet Express, but was not looking at putting this on users systems, i just wanted to create a couple of simple reports, what do other companies do? Do they use MS Query?

*This post is locked for comments

I have the same question (0)
  • Kine_ Profile Picture
    Kine_ 2,053 on at
    RE: Reporting in Excel - SQL Permissions?

    You can use e.g. Snapshot of the database for this (e.g. you can update the snapshot once per day). Than just allow users to read the necessary tables from the snapshot and you will be "safe" and the performance will not make problems on live db. But without permissions set on SQL, the "common" NAV user have no permission to read the tables from excel (or from anything outside NAV).

  • Ian Fletcher Profile Picture
    Ian Fletcher 2,445 on at
    RE: Reporting in Excel - SQL Permissions?

    We do not have sharepoint and have no SSRS reporting skills in house, so we are looking at a way of giving users the way of running their own reports until we decide on a True BI solution.

    So is this the preferred way of Reporting in Excel, to add a role in SQL to allow permissions to read certain tables?

  • Kine_ Profile Picture
    Kine_ 2,053 on at
    RE: Reporting in Excel - SQL Permissions?

    The Public permissions are allowing the users only to login to the database, nothing more. Granting permissions inside NAV will not grand any permissions directly on SQL. If you want to allow users to read data directly from SQL, you need to grand them additional permissions on SQL. E.g. create new role, assign permissions to the tables you need and than assign this role to users, which will use the report.

    Of course, you can use e.g. Sharepoint or Reporting services, where you can define the user account which will be used to access the data without having to allow the end-users to access the data directly.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

#1
Saurav.Dhyani Profile Picture

Saurav.Dhyani 2 Super User 2025 Season 2

#2
RK-25090803-0 Profile Picture

RK-25090803-0 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans