Still true for GP 2105R2 Security and GP 2016 Security.  Hands up who loves Dynamics GP / Great Plains security?

Hmm, ok, a couple hands up there. Well, you have to admit it’s much better since the change to the role based security with GP 2010. That relatively small change has allowed for managing security in a much easier fashion. You can now selectively assign or remove blocks of functionality with just a checkbox. Saves so much time and hassle. However, even though this has eased the burden of maintaining security, it still doesn’t help in the case that a user has been granted more access than they necessarily should have.

Granting users more access than required is a scenario we have all seen happen in many organizations, often for what are good (or at least expedient) reasons at the time. Assigning a user to the POWERUSER role for example. Given the granularity of GP security, it can be time consuming to determine what window, report or element the user needs to have added to their security to allow them to perform a particular function. This is doubly problematic as there is almost always a rush for these access requests, it’s month-end and a new user is needed to do some critical function, after trying a few roles and not finding the correct combination, the user gets assigned POWERUSER ‘temporarily’ to complete month-end, and then someone will ‘look at it later’.

Typically of course, later never comes along and the over assignment is left until it ( hopefully ) gets found at auditing time.

Now in many cases, this can be an issue tied into the necessary separation of duties between, payables, receivables etc for fraud management and just general good business practice. Sometimes though, it can be seen as a serious breach of what is privileged information. The payroll and HR modules come to mind immediately.

So, how can we improve GP security to what is likely sensitive information and still allow the users who are supposed to access it to be able to do so.

Well, one suggestion is to add another SQL security group and manage the data for the special module(s) with that as a second tier security check.

So what we will do in our example which will be for payroll, is we will remove the payroll table access from the DYNGRP role, and create a new role in the database called DYNPAYROLL, add the access to the relevant objects to that role then assign our nominated users to that role. This has the effect of ensuring that the users in the list have to pass both being assigned appropriate rights in SQL server as well as in GP. This being handled by two ( hopefully ) separate support staff ensures that there is a double check on users having access to the sensitive data.

Step 1. Create a new role in the company database called DYNPAYROLL. We will do this in the test company Fabrikam for our example, the database name is TWO.

Step 2: We will add access to the SQL server tables and stored procedures to our new role. There are also functions, views etc that can be also managed by access through the role, but for our purposes just controlling access to the base data should be sufficient to accomplish our goal. We need to isolate the tables and procedures we want to add access to our role and also later remove access from for the generic DYNGRP role. To do this, we will start with the tools in GP itself. We can narrow down the relevant tables by looking at the resource descriptions.

Here I have set the Series to Payroll, set the view by to Table physical name and the tables are listed in the scroll box.

Now, for our purposes we need to do some educated examination of the table names displayed, you will see there are a number that start DDXXXXXX which relate to direct deposit. These we will skip for our example. Scrolling down the list we find that there are others marked temp, but the core payroll tables all start with the prefix UPR.

Armed with the knowledge that our core payroll tables all start with UPR, now we can use some SQL tricks to gather them up. I could get clever and write a loop with some dynamic SQL to do this, but I am going for the KISS approach and will show you how to write a script that will build the script you need to remove and assign the permissions we need. The standard permissions assignment statement goes something like this. Grant Select, update, insert, delete on [Tablename] to [Username/Rolename].  For the stored procedures, the naming convention is zDP_UPR , ie the table names plus zDP_ as a prefix and then assorted suffixes.

That’s fine but if there are a lot of tables and procedures, who wants to key all that up ?  So let’s make the server do the work for us.

First we will make use of the system objects that holds the names of the objects in SQL server.  We will restrict our [type] to ‘U’ which is UserTable for the table and [type] =P for the procedures . You can dig further on this material in numerous SQL resources but I leave that to the reader as advanced SQL is out of scope of this blog.

Looking at our list, our names are matching the list we saw in the GP table resource view, so looks like we have the right set.

Next we will leverage this list to create the permissions statements we need to run. In our query tool, in my case SQL Server Management Studio, I am going to set the query results to be text.

After that we write a query to build a query that will do all the permissions assignments for us. If you look you can see I am combining the changing list of names from the query above and including a literal string to represent the permissions assignments. It quickly builds 90 ( there were 90 tables ) executable statements in the lower query results pane. Now we can copy the results of that lower pane, put it in a query window and execute all 90 permissions statements in one go.

And then for the procs, do the same.

After running this our DYNPAYROLL role has the insert, update, delete and select permissions it will require to the 90 Payroll tables.

Then we do the same but remove the permissions for the same tables from DYNGRP using the REVOKE command. There is also a DENY command but we will not use that as it trumps all other permissions and effectively locks the user or role from accessing the table until removed. Also I am avoiding discussion of schemas as the current GP implementation doesn’t really use them so for our purposes we will ignore them for now.

You can see the command has changed to REVOKE and the role was changed to DYNGRP.

Then we copy that result set into a new query and run it.

Looking at our list, our names are matching the list we saw in the GP table resource view, so looks like we have the right set.

Next we will leverage this list to create the permissions statements we need to run. In our query tool, in my case SQL Server Management Studio, I am going to set the query results to be text.

Then we write a query to build a query that will do all the permissions assignments for us. If you look you can see I am combining the changing list of names from the query above and including a literal string to represent the permissions assignments. It quickly builds 90 ( there were 90 tables ) executable statements in the lower query results pane. Now we can copy the results of that lower pane, put it in a query window and execute all 90 permissions statements in one go.

And then for the procs, do the same.

After running this our DYNPAYROLL role has the insert, update, delete and select permissions it will require to the 90 Payroll tables.

Then we do the same but remove the permissions for the same tables from DYNGRP using the REVOKE command. There is also a DENY command but we will not use that as it trumps all other permissions and effectively locks the user or role from accessing the table until removed. Also I am avoiding discussion of schemas as the current GP implementation doesn’t really use them so for our purposes we will ignore them for now.

You can see the command has changed to REVOKE and the role was changed to DYNGRP.

Once all that is done, our new DYNPAYROLL should have the access for payroll tables, and regular DYNGRP will not.

Now let’s test our theory. To grant access to payroll data successfully we will add our test user to the POWERUSER role just to show that they should have access to basically everything in GP.

At this point you can see the user is a member of the dyngrp in the TWO company, POWERUSER role so thus should be able to open payroll windows with no trouble.

When I open a payroll card I am presented with this.

Once that is done, let’s try opening our payroll user card.

Success !

Things to be aware of:

• If the user is logged in, you may need them to logout and in again for everything to take effect correctly.
• This should be done by someone experienced enough in SQL that they can correct what they have done if they make a mistake.
• Take a backup before doing any of this.
• Error messages will not be friendly so this technique is more for when access needs to be enforced regardless of what GP security settings may be changed to.
• While you can do this with other modules, the maintenance overhead starts increasing as you create more groups, and I would suggest this type of strategy really only fits well for payroll and HR which are a bit of a special case.
• You need to be aware that you will need to reset the security elements after performing a service pack or upgrade as typically the DYNGRP gets reset back to default after those types of system maintenance operations are completed.
• If you want to experiment with other modules, try to stick to just key core tables and related procedures as that will usually be sufficient to accomplish your goal. The biggest risk is removing access to a table or procedure from DYNGRP that is needed by other processes or modules eg SY system tables and the like.