What’s included in those SQL rpt roles?

Today’s #TipTuesday is a security tip to help you identify what exact permissions the SQL fixed database roles have, that start with rpt*. If you’re granting access to some of your windows users or groups, you want to know what they can get into, right?

The purpose behind the rpt* roles is generally for SSRS (SQL Reporting Services) reports that ship with Dynamics GP since users running those reports are outside of GP, not tying into GP permissions. There are plenty of other posts out there about GP & SSRS, so I am not going to get into that here. Instead I am going to show you a SQL query I’ve used to see what permissions are granted to what role, login, user or group in SQL.

SQL Query example

Here is a sample SQL query you can use to tell you what objects and permissions were granted to the rpt* roles in SQL, in a given Dynamics GP database.

-- SQL query - all SQL objects granted to #MSDYNGP rpt roles
-- Run against any Dynamics GP database
SELECT
USER_NAME(p.grantee_principal_id) as Grantee,
OBJECT_NAME(p.major_id) as ObjectName,
o.type_desc as ObjectType,
p.permission_name as PermissionName
FROM
sys.database_permissions p
inner join sys.objects o on p.major_id = o.object_id
WHERE
p.class = 1
AND USER_NAME(grantee_principal_id) like 'rpt%'
AND OBJECTPROPERTY(major_id, 'IsMSSHipped') = 0
-- ^^ this ignores objects created during SQL install itself
ORDER BY
USER_NAME(grantee_principal_id),
OBJECT_NAME(major_id), permission_name

The results of this query will resemble this:

If you know you haven’t altered anything the rpt roles have access to in your environment, you should be able to run this query against your system db (DYNAMICS or otherwise) + one Dynamics GP company because every Dynamics GP company that has the same products and modules installed/enabled would have the same result theoretically.

How I use this

I’m in the middle of converting various processes and reports in my organization to role-based security. Over the years different developers and consultants have written things for us, before my time here, and opted to use hard-coded SQL logins for things like SSRS reports and some other processes. That’s something I want to fix. IMHO, nothing should use a hard-coded login; access to data should be based on your role.

Since this isn’t the point of this particular post, I’ll simply say this: we are creating security groups in Active Directory by role. Those groups will be used to control access to the SQL objects required for a certain task at a granular level, instead of broadly giving users “db_datareader” or worse, “DYNGRP” permission on a db. Example: a given SSRS report needs select permission on table A, B and C to run. The roles that run that report will be granted select permission on table A, B and C only (or in some cases a SQL view so they can’t get to the underlying tables either if there is a security risk).

I am using this script to compare what access some of the hard-coded logins have, in order to grant that same access to a role-specific AD group in SQL instead. The WHERE clause in the above script – the bolded line – is specifically looking for rpt* items only but if you remove that, you get everything. I plan to look through the large default roles like DYNGRP to make sure no custom SQL objects are there and then look at all security granted specifically to a SQL login/user/group and investigate further.

Once I believe I’ve found and made all of the necessary changes for a given hard-coded login, I will change the password on those fixed logins so that any future use of it will cause an error trying access the data. That will be a temporary measure to determine where else something was used that hasn’t been documented. 🙂 Eventually the login would be removed entirely but it’s easier to leave it, and be able to change the password back if something is using it and deeper investigation is required to change that particular process.

It will take some time to work through these changes but ultimately our data will be much better secured in the end, more closely aligning to a “least permissions” methodology.

3 thoughts on “What’s included in those SQL rpt roles?

  1. Reply
    Barb Mehling - September 23, 2019

    Hi Jen.
    We have Custom Roles with Select permissions on groups of Tables and Views (IE. by Prefix) which were Granted via sql script.
    I have ACL groups assigned to the roles and this has been helpful for SSRS reports, Excel reports linked to SQL data, etc.

    We upgraded from GP2015R2 to GP2018R2 and somehow all these permissions were lost. We luckily had the Grant sql statements saved, but we had to run them for four custom roles across over 50 databases.

    Is there any way this could have been avoided?

    1. Reply
      Jen Kuntz - September 23, 2019

      Hi Barb,
      There is really no way to avoid that, no. Many permissions are lost during an upgrade because there are often drop/recreate procedures that occur (and various other reasons!). Saving the grant scripts is the way to go but there are also ways to create a stored procedure that could loop through your databases to run the same grant across all dbs with less effort than 1-at-a-time.
      Jen

      1. Barb Mehling - September 24, 2019

        Thanks for the quick reply Jen!

        I’ll look into a new script that loops through the db’s.

        Have a great day.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to top