And a One, And a Two, SQL Report Security

One of the most valuable (and yet underutilized) benefits of Microsoft Dynamics GP is the standard SQL Reporting Services (SSRS) reports that are available. These reports are valuable for a number of reasons including:

• Delivery via web to non-GP users

• Easy export capability to Excel, Word

• Ability to modify existing reports and access additional information

• Ad-hoc reporting capabilities using standard Report Models

• Subscription capabilities within SQL Reporting Services (delivers the report to your inbox!)

I know I have other posts dedicated to my love of SSRS, so I will spare you another love letter for now. But I do want to chat a bit about SQL Reporting Services security.

It seems like Dynamics GP users fall in to three main categories when it comes to security:

1. Everyone sees everything- Most common when the finance department is small and there is not a lot of delineation of duties. So often in GP, all users are POWERUSERS.
2. Limited access for convenience only- Maybe some security is defined, but only to help simplify the user experience. From a reporting perspective, there are no concerns about “protecting” information. This is usually the case when there is a delineation of duties, but due to overlap and backups, everyone knows everything anyway.
3. Limited access out of necessity- Security is defined not only to simplify the user experience, but also to protect data from unauthorized access. This is very common when payroll and human resources are included in the system, or in larger organizations with multiple offices/companies/entities to consider.
In the first two scenarios above, SQL Reporting security is not much of a concern. But in the third scenario, we would really need to be measured in our approach to ensure data is appropriately secured. I think, sometimes, it is assumed that customers fall in to #1 or #2 because of our understanding of their GP user base. But we need to be careful and consider the user base for SQL Reporting, and ensure that the customer understands its security model as well.

SQL Reporting security can be broadly categorized in to three areas:

• Site Security

• Item Security

• Data Security

Site Security is defined in Report Manager (generally http://servername/reports) using the Site Settings link. This is where you define who has permissions to manage and administer site level settings. Users do not have to be defined in Site Security to access and use SQL Reports. They only need this level of access to allow them to manage aspects of the site like system properties and schedules.

Item Security is also defined in Report Manager. This controls access to the folders and reports on the site. This can be accessed by choosing Properties or Security from the drop down list that appears when you click on an item. Or by using the Folder Settings button. By default, when an Windows user or group is assigned to an item in Report Manager, all items below inherit the access. For example, if a user is granted access to a folder, the same user also has access to the reports in the folder. This parent-level security inheritance can be broken by item by accessing security for the item. For example, if the user shouldn’t have access to one report within the folder, you can access security for that report and delete the user (therefore “breaking” the parent-level security inheritance).

Okay, so here is where it gets confusing :) Item level security controls access to the report. HOWEVER, being able to get to the report is NOT the same as being able to generate the report. This is where data security comes in to play. This can take on a lot of angles, depending on how you have configured the data source for your report. But let’s assume that you have configured it to use Windows credentials (that are not stored in the data source).

Data Security is defined in SQL Server Management Studio. This security controls the user’s ability to access the tables, stored procedures, and views necessary to generate reports. Keep in mind that although we are defining security for use with SQL Reporting Services, the data level security you define also allows users to access the data through Excel, Crystal Reports, Access, or another ODBC data source. For this reason, it is important that Data Security must be as restrictive as your requirements necessitate to prevent unauthorized access to data. Simply restricting access at the Item Level would only limit the user’s ability to access the data using that particular medium. If Data Security gives them access to the underlying data, they could access it with Excel, Access, etc even if they can’t access the SQL Report itself.

To configure Data Security in SQL Server Management Studio, the user must first be set up under Security>>Logins using their Windows login. Then, using the User Mapping tab in User Properties, you must map the user to the appropriate company databases and the corresponding database roles (to grant access to the necessary tables, views, and stored procedures to generate the reports).

Fortunately, Microsoft Dynamics GP comes with a number of predefined roles that begin with “rpt” specifically for use with the SQL reports. These roles have access to specific tables, views, and stored procedures. And the access is restricted so that data cannot be updated, only selected/viewed. Remember, whatever access you give them at the Data level can be used with Excel, Access, etc. So it is important, if you use roles outside of the standard “rpt” roles provided by Dynamics GP , to ensure that the roles do not grant additional permissions that you would not normally allow (e.g., updating tables, accessing additional tables, etc)

When planning security for your SQL reports, it is important to consider all three levels of security. There are resources on CustomerSource to assist with this process including:

Frequently Asked Questions About SQL Reporting Services and Dynamics GP

Download the SQL Reporting Services Administration Guide (last updated for GP 10), which includes additional information on security

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.