GP: Retrieve List of Company Names & IDs

Here’s a script to return you a quick list of the company ids (which are also the database name) and the company names for the companies defined in the DYNAMICS database

SELECT INTERID,CMPNYNAM FROM SY01500
ORDER BY CMPNYNAM

GP: Reset the System Password

Working as a GP Consultant, I occasionally run into a situation where a client doesn’t remember their System Password for GP. You know, the one that let’s you add users, change company information, the admin level tasks in GP.

The only supported solution is to contact MBS Support, who will walk you thru a process to gather information to send them.

There’s an easier way. Run the following script in SQL to reset the system password to blank. You can then go back into Setup and set it to whatever you want.

update DYNAMICS.dbo.SY02400
set PASSWORD = 0x00202020202020202020202020202020
WHERE DMYPWDID = 1

GP: Error setting User Access to Company

In Dynamics GP, administrators have the ability to control what companies users can access. This also controls what users see when the click the drop-down company field during login.

The menu instructions for this post are for GP 9.0. Newer versions provide the same functionality, although menu layout may be slightly different.

To change User Access for a user, click Tools / Setup / System / User Access. Clicking a username will display all of the available databases on the right and you can click the checkbox to allow or disallow access to specific companies for that user.

Every once in a while, especially have migrating to a new server or merging companies, you’ll get an error message “The user could not be added to one or more database”

A typical reason for this is that on the SQL side of the installation, the GP user already exists as a SQL user on the database, but GP doesn’t know about it. When you click the checkbox, GP tries to add that user to the database users. Since the object already exists, SQL kicks back an error that GP doesn’t know how to deal with.

To resolve the error, open SQL Management Studio and connect to your GP SQL instance. Then, run the following script, replacing <dbname> with the Database name of the company you’re trying to add the user to and replacing <username> with the user’s SQL login id. Our example is for company “Jim’s Bike Shop” that has a database name of JBS and the user id being jfallon.

USE <dbname>
DROP USER <userid>

So for our example, the code would be

USE JBS
DROP USER jfallon

Go back into GP and you should now be able to add the user to the company.