Friday, June 7, 2013

Microsoft SQL Server security roles and Microsoft Dynamics GP SSRS reports

It's not uncommon to find system administrators and consultants alike struggling to deploy Microsoft Dynamics GP SRS reports in an environment, while trying to keep these reports secured. I find that the main issue revolves around understanding the different moving parts: domain accounts, GP user logins, the SQL Server database roles created for reporting, the reports web site security, the reports folder security and the list goes on.

So here is a list of resources that I believe will help with this:

The best place to start is the SQL Server Reporting Services Guide, which has all of the information for installing, deploying and setting up security for SRS reports to use with Dynamics GP. This guide is available for download from the Microsoft Download Center.

Microsoft Dynamics GP 2013 Guide: SQL Server Reporting Services

In order for users to access SRS reports (default) for Dynamics GP, they need to have the following permissions:

1. Access to the Reporting Services web site (native mode) or report library (SharePoint Integrated). I have found the following resources to be extremely useful when configuring Reporting Services security in native mode:

Granting Permissions on a Native Mode Report Server

If you are dealing with Reporting Services in SharePoint Integrated mode, the following is a good resource:

Security Overview for Reporting Services in SharePoint Integrated Mode

2. Permissions to the SRS report files themselves. The following TechNet video shows just that:

3. Database security roles (RPT_ ) under the DYNAMICS and/or company databases in order to pull data into the SRS reports. You will want to take a look at the following KB article 954242 - Frequently asked questions about the integration of SQL Server Reporting Services (SSRS) with Microsoft Dynamics GP 10.0 and Microsoft Dynamics GP 2010, which can be found at:

I hope you find these resources useful.

Until next post!

Mariano Gomez, MVP
IntellPartners, LLC


Beat BUCHER said...

Thanks Mariano,
this topic is very hot at the moment, so this is welcome information :-) and very useful...

Alonzo said...

This is cool!