Enforcing field level security in Dynamics GP

Field level security is a useful Dynamics GP feature. It used for easily securing a field or form, however I have seen the admin user interface form confuse IT admins who are not used to the way GP does things, or simply mistakes being made setting up security, leading some users not having the correct security applied.

The problem I am solving in this post is where some of the field level security policies need to be always enforced/applied to (almost) all users  in GP. To solve this I wrote a quick SQL script to enforce the field level security policies for one of the GP companies. The script ensures that all users who are not in the SYSADMIN class, have the supplied list of field level security policies applied to them, for a particular company. This can be put in a stored procedure and ran within a loop to apply to a list of GP companies, should that be required. It could also be set to run at frequent intervals via a SQL scheduled job, for peace of mind.

If automating the script I would recommend introducing a step in the script to check all the field security IDs hard coded in the script still exist, perhaps raise an exception if they don't so the SQL job will report a fail to admins. For simplicity I have omitted that stage in the example below.

--    Script will apply LISTED field level security ids 
--    to ALL users, that are not of class of SYSADMIN 
--    for the GP company id specified
--
--  Intention of script is to prevent human error in setting field level security 
--  allowing users to slip though and not having it applied to them.
--  It will do this enforcing the security on selected company for all those users.
--  T.Wappat 2016-11-09
--    Check the validity of this script on a test instance before running in production
--  Author assume no responsibility for errors and omissions, or for damages resulting 
--    from the use of the information contained herein.

DECLARE @CMPANYID as smallint 
SET @CMPANYID=2
MERGE WDC41500 AS target
USING (
    SELECT ut.USERID
        ,ua.CMPANYID
        ,secid.Field_Security_ID
    FROM SY01400 ut
    JOIN SY60100 ua ON ut.USERID = ua.USERID
    CROSS JOIN [WDC41300] secid
    WHERE ua.CMPANYID = @CMPANYID
        AND ut.UserStatus = 1
        AND secid.Field_Security_ID IN (
            'IV_INACTIVE'
            ,'PRICELEVEL'
            ,'SOPSHIPTO'
            ,'SOPSHIPTO2'
            )
        AND USRCLASS != 'SYSADMIN'
    ) AS Source(UserID, CompanyID, Field_Security_ID)
    ON source.USERID = target.USERID
        AND source.CompanyID = target.CMPANYID
        AND target.Field_Security_ID = source.Field_Security_ID
WHEN NOT MATCHED BY target
    THEN
        INSERT (
            CMPANYID
            ,USERID
            ,Field_Security_ID
            )
        VALUES (
            2
            ,source.USERID
            ,source.Field_Security_ID
            );

Even if you don’t have the same example problem to solve, the above script may be a leg up and help orientate the reader so they can solve the problem that they do have. Apologies that it is in a MERGE statement, when I started writing it, I was expecting it to get more complicated than it ended up being, an insert on a join would have done this too.