Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
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

  • 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

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans