Quickly Generate Access Rights via TSQL

Hello all

For those that don’t know, the security setup has changed with Acumatica 2024R1.  When installing a customization for the first time, it is now required that Access Rights to your custom screens be specifically stated otherwise they will not be available to any user/role.

“If an administrative user adds a new site map node directly on the Site Map (SM200520) form, the system will automatically set the form’s access rights to Revoked for all user roles. Then the user should grant access to this form to particular user roles on the Access Rights by Screen (SM201020) form.”

Our product has over 140 pages and I needed to create an Access Right for each one.  While I could have added each one manually, I chose to create the xml for the project.xml via a TSQL script and paste the result into my customization.  While I probably didn’t save much actual time, perhaps I can save some for you!  Below find my quick and dirty script.  You may need to customize the script in the following ways:

1. Change the @companyid and the @searchphrase variables to match what you’re doing. 
2. I chose to apply the ‘ApplyAndKeep’ rule
3. I’ve given access rights to the Administrator and Acumatica Support Roles

Happy Coding!

/*******************************

Create Access Rights XML

2024 psc SPS

create Access Rights for each Page with your dev prefix

********************************/

DECLARE @cmd Varchar(500),@cnt int,@screenID varchar(10),@searchphrase varchar(10),@minKey int,@tempcnt int,@companyid int, @pos VARCHAR(10),@title varchar(255),

@url varchar(512),@nodeID uniqueidentifier,@parentid uniqueidentifier,@selected varchar(1)

SELECT @cnt = 0,@minKey = 0,@screenID=''

--Customize these

SELECT @companyid = 2, @searchphrase = 'ISPS%', @selected = '3'

WHILE (1=1)

BEGIN

       SELECT @cnt = @cnt +1

       SELECT @tempcnt = Count(*) from sitemap where  companyid = @companyid and ScreenID > @screenID      

       if (@tempcnt = 0)

              BREAK;

       SELECT @screenID = Min(screenID) from sitemap where companyid =@companyid and screenid like @searchphrase and screenid >  @screenID

       select @pos= CONVERT(VARCHAR,COALESCE(Position,0)),@title = Title,@url = Url,@nodeID = NodeID,@parentid =ParentID,@selected= coalesce(SelectedUI,'E') from SiteMap where companyid =@companyid and screenid = @screenID

       print @screenid

       select @cmd = '<ScreenWithRights AccessRightsMergeRule="ApplyAndKeep">'

       print @cmd

       select @cmd = '      <data-set>'

       print @cmd

       select @cmd = '             <relations format-version="3" relations-version="20240201" main-table="SiteMap">'

       print @cmd

       select @cmd = '                   <link from="RolesInCache (ScreenID)" to="SiteMap (ScreenID)" />'

       print @cmd

       select @cmd = '                   <link from="RolesInGraph (ScreenID)" to="SiteMap (ScreenID)" />'

       print @cmd

       select @cmd = '                   <link from="RolesInMember (ScreenID)" to="SiteMap (ScreenID)" />'

       print @cmd

       select @cmd = '                   <link from="Roles (Rolename, ApplicationName)" to="RolesInCache (Rolename, ApplicationName)" type="FromMaster" updateable="False" />'

       print @cmd

       select @cmd = '                   <link from="Roles (Rolename, ApplicationName)" to="RolesInGraph (Rolename, ApplicationName)" type="FromMaster" updateable="False" />'

       print @cmd

       select @cmd = '                   <link from="Roles (Rolename, ApplicationName)" to="RolesInMember (Rolename, ApplicationName)" type="FromMaster" updateable="False" />'

       print @cmd

       select @cmd = '             </relations>'

       print @cmd

       select @cmd = '             <layout>'

       print @cmd

       select @cmd = '                   <table name="SiteMap">'

       print @cmd

       select @cmd = '                          <table name="RolesInCache" uplink="(ScreenID) = (ScreenID)" />'

       print @cmd

       select @cmd = '                          <table name="RolesInGraph" uplink="(ScreenID) = (ScreenID)" />'

       print @cmd

       select @cmd = '                          <table name="RolesInMember" uplink="(ScreenID) = (ScreenID)" />'

       print @cmd

       select @cmd = '                   </table>'

       print @cmd

       select @cmd = '                   <table name="Roles" />'

       print @cmd

       select @cmd = '             </layout>'

       print @cmd

       select @cmd = '             <data>'

       print @cmd

       select @cmd = '                   <SiteMap>'

       print @cmd

       set @cmd = '                      <row Position="'+ @pos +'" Title="'+@title+'" Url="'+@url+'" ScreenID="'+@screenID+'" NodeID="'+convert(nvarchar(36),@nodeID)+'" ParentID="'+convert(nvarchar(36),@parentid)+'" SelectedUI="'+@selected+'">'

       print @cmd

       select @cmd = '                                 <RolesInGraph Rolename="Administrator" ApplicationName="/" Accessrights="4" />'

       print @cmd

       select @cmd = '                                 <RolesInGraph Rolename="AcumaticaSupport" ApplicationName="/" Accessrights="4" />'

       print @cmd

       select @cmd = '                                 <RolesInGraph Rolename="*" ApplicationName="/" Accessrights="0" />'

       print @cmd

       select @cmd = '                          </row>'

       print @cmd

       select @cmd = '                   </SiteMap>'

       print @cmd

       select @cmd = '                   <Roles>'

       print @cmd

       select @cmd = '                          <row Rolename="Administrator" ApplicationName="/" Descr="System Administrator" Guest="0" />'

       print @cmd

       select @cmd = '                          <row Rolename="AcumaticaSupport" ApplicationName="/" Descr="Role for Acumatica Support. Access similar to Administrator except for User Management, Security Settings and Access Management." Guest="0" />'

       print @cmd

       select @cmd = '                   </Roles>'

       print @cmd

       select @cmd = '             </data>'

       print @cmd

       select @cmd = '      </data-set>'

       print @cmd

       select @cmd = '</ScreenWithRights>'

       print @cmd

END