Skip to main content

Notifications

Announcements

Check out the New Agent Creator Community
Announcing new navigation menus and the Engage with the Community forum!
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

  • Tami Farrelly Profile Picture
    Tami Farrelly 5,080 on at
    RE: Excel Reports

    again...thank you Beat for your help!!

    greatly appreciate it!

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

    Hi Tami,

    There used to be a KB document from Microsoft on Customer Source (or Partner Source) about the default RPT_ roles in SQL server...  Just can't seem to find it right now.

    Mariano Gomez (MVP) has published back in 2011 a blog post where he describes the issue with assigning the GP users to the role.. He even provides a script to somehow try to automate the process, though it may not fit within your organization, as it assumes that the GP users are defined based on the same login ID as the domain ID (since we're talking about Windows Authentication here).

    Will try to find the KB document for you and post up the link here.

    PS: Here's the link to another blog post that explains the roles and provides the URL for the customer source download of the PDF file: http://www.fmtconsultants.com/2013/02/security-considerations-for-microsoft-dynamics-gp-2010-sql-reporting-service-reports/

    Edit (2020): The above link doesn't work anymore.. but to answer Tami's question about what each rpt_ roles covers as SQL objects, Jen Kuntz wrote a blog post in 2019 that would talk about that specific need to find out the related object. Her T-SQL query needs to be run again the DYNAMICS or company DB's and list all the SQL objects (views, SP's, tables, etc) granted permissions to any rpt_ roles. You could of course this script to check other SQL security roles (not GP roles!): https://jenkuntz.ca/2019/03/whats-included-in-those-sql-rpt-roles/

  • Tami Farrelly Profile Picture
    Tami Farrelly 5,080 on at
    RE: Excel Reports

    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?

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

    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.

  • KirkLivermont Profile Picture
    KirkLivermont 5,985 on at
    RE: Excel Reports

    Hi Tami,

    Yes.

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

    Regards,

    Kirk

  • Tami Farrelly Profile Picture
    Tami Farrelly 5,080 on at
    RE: Excel Reports

    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?

  • Tami Farrelly Profile Picture
    Tami Farrelly 5,080 on at
    RE: Excel Reports

    Thanks for the link Beat!!

    Tami

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

    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

  • Verified answer
    KirkLivermont Profile Picture
    KirkLivermont 5,985 on at
    RE: Excel Reports

    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

  • Tami Farrelly Profile Picture
    Tami Farrelly 5,080 on at
    RE: Excel Reports

    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...

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

Check out the New Agent Creator Community
Announcing new navigation menus and the Engage with the Community forum!

Quick Links

Jainam Kothari – Community Spotlight

We are honored to recognize Jainam Kothari as our June 2025 Community…

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Almas Mahfooz Profile Picture

Almas Mahfooz 3 User Group Leader

Last month Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans