Today's #TipTuesday is a security tip to help administrators identify what exact permissions the SQL fixed database roles have, that start with rpt*. When granting access to some of Windows users or groups, admins want to know what they can get into, right?

The purpose behind the rpt* roles was 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 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 to identify what objects and permissions were granted to the rpt* roles in SQL, in a given Dynamics GP database. The same query can be tweaked further in the WHERE clause for other purposes too.

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

ORDER BY
  USER_NAME(grantee_principal_id),
  OBJECT_NAME(major_id), 
  permission_name

The results of this query will resemble this:

Results of the SQL query showing a sample db role and objects and permissions assigned.

If the rpt roles have not been altered in the environment, whoever runs this should be able to run this query against the 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 do I utilize 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 a user's 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 database.

  • Example: a given SSRS report needs select permission on tables A, B and C to run. The roles that run that report will be granted select permission on tables 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, 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 that part of the clause is removed, the result will be permissions assigned to any user or group. 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 when trying to 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.