Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics GP forum
Suggested answer

Security needed to run SSRS reports

Posted on by 75,730

What security to grant users to allow them to run SSRS reports. I have this one client where they cannot run any of the SSRS reports.They get the message in the screenshot.  All I ever do is go into SSRS and grant domain users access to the main folder. This is leading me to believe I need to also go into SSMS and add these domain users to the databases.

SSRS_2D00_Security.png

Categories:
  • Suggested answer
    RE: Security needed to run SSRS reports

    I agree. I've already made plans to get this information published into a blog and then I think it may not be a bad idea to update our SQL SRS guide that we had in the past, which had configuration, checklist, installation, deployment and security setup information in it, but was last updated around SQL Server 2012.

    Probably get that posted out on the Microsoft DOCS site now that we're putting our documentation there with PartnerSource and CustomerSource being no more.

    Thanks for the input........

  • Victoria Yudin Profile Picture
    Victoria Yudin 22,766 on at
    RE: Security needed to run SSRS reports

    Derek,

    It would be really great to have this written up in detail in a blog post or something like that. SSRS security is a persistent challenge for many Dynamics GP administrators.

    Thanks,

    Victoria

  • Suggested answer
    RE: Security needed to run SSRS reports

    We used to have documentation for SSRS reports, deployment and such, but those were probably last updated around GP 2013 or so, so may be something we need to put out in a blog or on the DOCS site with everything else moving over there, updating it for newer versions of SQL which have made minor changes with Reporting Services and such.

    Thanks

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Security needed to run SSRS reports

    Thank you Derek, I believe this is the first time I have seen these details listed.

  • Suggested answer
    RE: Security needed to run SSRS reports

    I usually explain permissions for SQL SRS reports, deployed from Microsoft Dynamics GP, as the following:

    >>For SQL SRS Reports, there is three areas where permissions need to be given:

    1. In the SQL SRS Report Manager site, click on Site Settings > Security. Click on the 'Security' tab, and then click 'New Role Assignment' in order to assign the users/groups as a System Administrator or a System User, depending on what they need permissions to do. (If at all possible, set it up per user, not per user group, as we've seen various results when using user groups.)

    2. Also in Report Manager, click on Home > 'Folder Settings', on the Security tab, click 'New Role Assignment' and assign users/groups one or more of the roles as needed: Browser, Content Manager, My Reports, Publisher, Report Builder.

    3. Lastly, in SQL Server Management Studio, expand the main Security folder, then expand Logins. Find the Windows user account that you're giving access to the SQL SRS reports and right-click on it, and choose Properties.

        In the Login Properties window for that Windows user, click on the 'User Mapping' page.

    Here, select the DYNAMICS/system database for Dynamics GP and in the list of database roles, you'll see about a few RPT_ roles. Mark the 'rpt_all user' role, which is required for all users, and then assign the user any of the other rpt roles needed. If you want the user to have access to all SQL SRS reports and/or Excel Report data, you can assign them the 'rpt_power user' role, which is similar to the PowerUser security role in Dynamics GP.

    In any of the company databases that the user needs to access SQL SRS reports from, highlight those companies as well in this Login Properties window and under the list of database roles, you'll again see a series of rpt roles for different areas, similar to the security roles in Dynamics GP. Assign the user to the rpt roles the user needs to access the SRS reports, or give the user the 'rpt_power user' role if you want them to have access to all SQL SRS report data.

    Click OK and close the window to save changes.

    Let us know if this doesn't help resolve the SSRS report issues or you have any questions.

    Thanks

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Security needed to run SSRS reports

    Lisa, all the links in this KB are now broken. I will gather domain names of users and them and their roles to SQL.

  • Lisa at AonC.com Profile Picture
    Lisa at AonC.com 3,024 on at
    RE: Security needed to run SSRS reports

    I think that deployment only creates the roles, not grant them to users.  

    Here is a slightly-dated KB FAQ article:

    support.microsoft.com/.../frequently-asked-questions-about-the-integration-of-sql-server-reporting-services-ssrs-with-microsoft-dynamics-gp-10-0-and-microsoft-dynamics-gp-2010-1272e0b8-3346-9e23-181d-f19444446732

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Security needed to run SSRS reports

    Lisa, what roles, if any, does GP grant when deploying these reports?

  • Suggested answer
    Lisa at AonC.com Profile Picture
    Lisa at AonC.com 3,024 on at
    RE: Security needed to run SSRS reports

    I think you are on the right track with SSMS Security configuration.  You should see roles that begin with rpt_ (for example, rpt_all user) to assign to the user.  (The roles will look very similar to GP security roles).  In addition to giving the network login access to the company database, you also need to give the user access to the DYNAMICS database.

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

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,537 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,520 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Product updates

Dynamics 365 release plans