Finance | Project Operations, Human Resources, ...

Security needed to run SSRS reports

(0) Share

Report

Posted on by Richard Wheeler

75,848 Moderator

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.

Categories:

Microsoft Dynamics GP

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

Lisa at AonC.com 921 Super User 2025 Season 2 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

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

Was this reply helpful? Yes No
Lisa at AonC.com 921 Super User 2025 Season 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

Derek Albaugh on at

Like (1)

Report

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

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

Derek Albaugh on at

Like (1)

Report

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

Was this reply helpful? Yes No
Victoria Yudin 22,769 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

Derek Albaugh on at

Like (2)

Report

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

Was this reply helpful? Yes No