However, we found that at some point in the past, all company access had been removed from the sa user account leaving us unable to log into Dynamics GP.
Fortunately, company access is only stored within one table in the system database: User-Company Access (SY60100).
The SQL below will add company access back to the sa user for all company databases:
/* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */ INSERT INTO SY60100 (TRKUSER,USERID,CMPANYID,SRBCHSEC_1,SRBCHSEC_2,SRBCHSEC_3,SRBCHSEC_4,SRBCHSEC_5,SRBCHSEC_6,SRBCHSEC_7,SRSFNSEC_1,SRSFNSEC_2,SRSFNSEC_3,SRSFNSEC_4,SRSFNSEC_5,SRSFNSEC_6,SRSFNSEC_7,MSCPRMIS) --VALUES ( SELECT 0,'sa',CMPANYID,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF FROM SY01500 AS ['Company Master'] WHERE ( SELECT COUNT(*) FROM SY60100 AS ['User-Company Access'] WHERE ['User-Company Access'].CMPANYID = ['Company Master'].CMPANYID AND ['User-Company Access'].USERID = 'sa' ) = 0 ) GO
After you’ve run the above to add company access back, you also need to run the SQL insert statement in this post to add POWERUSER access as well.
With the two scripts run, the sa account can be used to reset the DYNSA user and other user accounts.