Dynamic Data Masking with SQL 2016 and Dynamics GP 2016 – 2

Welcome back campers!  Todays post will guide you through implementing Dynamic Data Masking inside of Dynamics GP 2016 via Microsoft SQL 2016.  I again would like to iterate this is not an alternative to traditional data security.  DDM only modifies the display of the data in the result set of a query. 

Lets have a look the test environment I have setup.  I am running Microsoft SQL 2016 Developer and Microsoft Dynamics GP 2016 atop of Microsoft Windows 2012 R2.  I have created two test users test1 and test2, I know original right? I have assigned each test user to the same company and security roles inside of Dynamics GP.


The next step I took was to login and validate that I did see the SSN in Cards > Payroll > Employee which is shown below and as expected I can fully see the SSN in the card.


Now we want to prevent that from being seen by anyone who should not see this information which is going to be test1, as test2 has been promoted to a manager and should be able to see this information. So now we make the move over to SSMS and open up our SQL environment and start a new query.


So the code above outlines the basics of DDM with GP 2016.  In this scenario we are going to setup the default masking on the SSN column inside of UPR00100 which is the payroll master table inside of GP 2016.


In the code above I’ve told SQL 2016 that I want to place a mask on the SOCSCNUM field inside of the UPR00100 table n the TWO database.  The thing to note is that you must specify the column type and length.  This way DDM knows how many possible characters may be present that need masking. I have executed the code above while test1 is still logged into GP let us take a look at that same record again w/out the need to log out.


Well sure enough the SSN column presents the masking as we expected.  So the next thing we’ll do is examine to make sure the same results occur for test2 as we haven’t modified their ability to see DDM data yet, and the results are the same.


Now we want to allow test2 the ability to see this masked information w/out changing their role inside of Dynamics… Because we all know how this ends up with all kinds of crazy roles for one off things or some other crazy solution. BACK TO SQL!


We have now told SQL that we want user test2 to be allowed to read masked data. The important thing note here is that we did not specify which column the test2 user would have access to, and the MSDN information did not specify either, so I make the wild assumption this allows any user who is granted this right would be allowed to view all masked columns. I will be sure to dig deeper on this thought.

I now clear the card and open the same employee as before and I as test2 am able to see the data unmasked exactly as expected.


I have now shown how this works at the card level inside of Dynamics GP 2016 but I want to know if this carries over to other aspects of Dynamics GP 2016 such as Smart List and so on.  Well lets find out shall we? I open the default Smart List object Employee List and add the column for SSN to it while logged into Dynamics GP 2016 as test1 who should never see the SSN information.


The behavior was exactly as expected, users test1 was unable to see the masked data. Lets validate test2 gets the same results shall we?


Well would you look at that?  Just look at it!!! The results turned out as you probably would have guessed, our user test2 was able to view the unmasked data.




I thought this would wrap up this post without dragging it out into a 3 or 4 part series, but this afternoon Tim Wappat [t] and Kenneth Fisher [t] chimed in with some amazing questions which I believe I have the answer to or at the best practical explanation of what is going on.  I’ll be outlining their thoughts with my results in a surprise 3rd part series blog post.  I’m not fully sure on the ETA but I’d venture next week would be a good time estimate as it allows me to fully test the various scenerios to see what I come up with.

1 thought on “Dynamic Data Masking with SQL 2016 and Dynamics GP 2016 – 2

  1. Pingback: Dynamic Data Masking with SQL 2016 and Dynamics GP 2016 – 3 – Jonathan P Cox

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.