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

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Excel Reports
Microsoft Dynamics GP (Archived)

Excel Reports

(0) ShareShare
ReportReport
Posted on by Tami Farrelly 5,080

I am trying to deploy excel reports outside of the Great Plains...for users that do not have access to GP..but have a SQL license....I would like to give them the ability to refresh the data as needed

I am customizing reports and want to give permissions to various groups in our organization...such as finance, sales, inventory production engineering etc.

I do not want to give a select on all tables to these users....sales does not need access to finance..engineering does not need access to sales\finance

and no one needs access to payroll.

I cannot find relevant documentation on deploying this...other then adding users to the sql groups...and using grant on all tables

does this not give the users the ability to write a select statement on our payroll tables???

Can someone point me to the correct documentation on setting this up without giving everyone access to every table?

*This post is locked for comments

I have the same question (0)
  • Tami Farrelly Profile Picture
    Tami Farrelly 5,080 on at

    I continue to get

    Connection Failed

    SQLstate '42000'

    Sql Server Error: 18456

    Login failed for user 'xxxx'

  • Verified answer
    KirkLivermont Profile Picture
    KirkLivermont 5,985 on at

    Hi Tami,

    When you publish the report select the relevant security groups to publish to. From there make sure the user is a member of that sql report security group. There is no need to  (and many reasons not to) grant select on all tables for the users.

    Regards,

    Kirk

  • Tami Farrelly Profile Picture
    Tami Farrelly 5,080 on at

    Thanks for the reply Kirk...this sql report user group...is that outside of GP??

    the users I want to give access to these reports are not GP users...

  • Verified answer
    KirkLivermont Profile Picture
    KirkLivermont 5,985 on at

    Hi Tami,

    You will need to add a login for their domain account on the SQL server that has the appropriate reporting permissions.

    Regards,

    Kirk

  • Verified answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at

    Hi Tami,

    As Kirk suggested, it is mandatory that you add in the SQL security the domain user ID for the users you want to grant access to GP data.. Once the Domain User ID has been added to SQL security, you have to give them access to the individual GP company databases and the DYNAMICS DB eventually. Microsoft has built-in some useful default SQL security roles for reporting purposes, in order to grant access to users for the Excel & SSRS reports that come out-of-the-box with GP. You can use the same groups to assign to your users, so you don't have to struggle with individual table or view security.

    Those default roles are all starting with rpt_xxxx in SQL.

    Here is an excellent blog post from Mariano Gomez about the SQL report security with a link to the list of roles from Microsoft : dynamicsgpblogster.blogspot.ca/.../microsoft-sql-server-security-roles-and.html

  • Tami Farrelly Profile Picture
    Tami Farrelly 5,080 on at

    Thanks for the link Beat!!

    Tami

  • Tami Farrelly Profile Picture
    Tami Farrelly 5,080 on at

    Hey there Kirk..

    Is that using User Mapping??

    Do you know if I can use a Domain Group to map to the Sql RPT users?

  • KirkLivermont Profile Picture
    KirkLivermont 5,985 on at

    Hi Tami,

    Yes.

    I believe using a domain group will work but I haven't ever actually tested it.

    Regards,

    Kirk

  • Verified answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at

    Hi Tami,

    Yes, AD security groups work the same way as users... I use  them for some project reporting and its even better since you only have to manage the users on the AD side, once the group has been added to SQL and granted permissions through the RPT roles.

  • Tami Farrelly Profile Picture
    Tami Farrelly 5,080 on at

    Ok...I think I have the answers I need...except one

    Is there a way to view what tables these built in rpt users have access too...

    I just want to make sure I assign the proper sql rpt user to the proper users as I have somefiddlers...and do not want them to be able to select from say finance or payroll tables.

    Is there a KB or a way to find what tables the RPT users have access to?

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

Responsible AI policies

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

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans