azurecurve

We’re busy doing some work for a client for whom we’ve recently taken over the support of their Microsoft Dynamics GP implementation. For the initial set of projects, we’re assisting them in the creation of a standalone test systemm. When you do this, the first thing you need to do is log in using the sa account and reset passwords.

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