Skip to main content
Post a question

Notifications

Announcements

No record found.

SQL Server Reporting Services Reports for Microsoft Dynamics GP Permissions Explained.

We've gotten this question a bit lately, so figured it made sense to put it out to the public as a reference that can be found fairly easy, when you are setting up and deploying the SQL Server Reporting Services (SRS) reports for Microsoft Dynamics GP.

The first thing I'd point out is that permissions to the SQL SRS reports for Microsoft Dynamics GP are given to a user's Windows account, not their Dynamics GP login. This allows them to access the SSRS reports whether inside the Microsoft Dynamics GP application or outside of it.

The permissions needed by a user to access the SQL SRS reports for Microsoft Dynamics GP and view data are three parts: Permissions to the SQL Reporting Services site itself where the reports are deployed, the folders and report files themselves, and then the GP data that is pulled by these SSRS reports.

These permissions are setup as follows:

A. Permissions to the SQL Reporting Services site

In the SQL SRS Report Manager site, at the site level, 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.).

B. Permissions to the folders and report files themselves

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.

Most users will only require the Browser role while a smaller amount of users will need the Report Builder or Publisher role. Even fewer should be given the Content Manager role. (Again, if at all possible, set it up per user, not per user group, as we've seen various results when using user groups.).

C. Permissions to the GP data that is pulled into the SQL SRS reports

This is done using the RPT_ database roles that exist in the system and all company databases for Microsoft Dynamics GP.

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

You may or may not need to restart the SQL Reporting Services service to push changes down before testing access to the SQL SRS reports for Microsoft Dynamics GP.

Notes:

--Beginning with Microsoft SQL Server 2017 and later, SQL Reporting Services is a separate install from the main Microsoft SQL Server install where, in past versions, we could include SQL Reporting Services as an additional feature to install.

--These permissions are for a Native Mode configuration of SQL Reporting Services. For granting permissions on Reporting Services items on a SharePoint site, you can look at the following document:

     https://docs.microsoft.com/en-us/sql/reporting-services/security/granting-permissions-on-report-server-items-on-a-sharepoint-site?view=sql-server-ver15

--If you have Microsoft SQL Server installed on one server and SQL Reporting Services on another server, in order to access the SQL SRS reports from a third machine, you will need to use Kerberos Authentication over Windows Authentication. This creates a 'double hop' situation which won't allow the user's Windows credentials to be passed all the way through.

Hopefully this will answer some questions on what permissions are needed to allow users access to the SQL Reporting Services (SRS) reports deployed from Microsoft Dynamics GP as well as to view the data the SSRS reports are pulling from the GP databases.

While there may be some slight differences between SQL Reporting Services versions, the three levels of permissions required for users to access the SSRS reports remains the same regardless of the Microsoft SQL Server/SQL Reporting Services version and/or Microsoft Dynamics GP.

Thank you!

Comments

*This post is locked for comments