Add Company Access Back to sa User

Microsoft Dynamics GPWe’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 (http://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.

Problems Logging into Microsoft Dynamics GP as sa After Password Change

Microsoft Dynamics GPI’ve been working with a client recently on an upgrade to Microsoft Dynamics GP 2018 and all has gone well overall. However, they had a security project running at the same time, where they were changing the passwords for a number of accounts including the SQL Srver sa account.

Unfortunately, the password length they were setting was 20 characters,. They supplied the new password to me, but I found I couldn’t log in. When I then tried to copy and paste the password into the field, nothing happened (which is what clued me into the issue).

The cause of this login issue, is that the Password field on the Users Master (SY01400) table has a maximum Keyable Length of 15 characters, and this restriction is enforced on all user ids entered on the login window.

So for me to complete the final steps of the upgrade, the password for the sa account was changed to a 15 character one; after the upgrade was complete, the password was again changed to a longer one and then the account disabled.

Best practice is to use the sa account to administer only SQL Server and to use either a named user account in Dynamics GP or to use the DYNSA account. I am encouraging all clients to ensure this is the case.

There is a wide range of ways you can better secure your system. One way of working through these is to engage with your partner and another is to purchase the Microsoft Dynamics GP Security and Audit Field Manual: Dynamics GP 2016 book by MVP Mark Polino and Andy Snook. This book is for the 2016 version of Dynamics GP, but still fully applies to Microsoft Dynamics GP 2018 (as well as earlier versions).