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.