Understanding the DYNAMICS Database in Microsoft Dynamics GP

How Dynamics GP databases are arranged?

Microsoft Dynamics GP is designed on a central database (named DYNAMICS) and company databases. So to install a single company in GP at least two databases are needed (i.e. DYNAMICS and a company database).

As such, let’s say you have 3 companies configured. In such a case four databases will need to be installed in SQL Server. Such as:

All companies will have their respective information i.e. master data, transactions, etc. stored in their own company databases.

So what’s the purpose of the DYNAMICS database? Below we outline some of the features and other aspects of the DYNAMICS database.

DYNAMICS Database features

All the information which is not specific to any company is maintained in the DYNAMICS database. Below is some of the commonly required information that is stored in the DYNAMICS database.

User Master

User Class Master

Company Master

System Password

Users Notes

Batch Activity

Registration Information

User Activity

Alert Messages

Currency setup

Activity Tracking

User Tasks

User Roles

 

GP security in a few words

When GP is installed or a company is created, GP creates a SQL group named “DYNGRP”. This group has all the rights to perform any task in the GP databases.

For every user created in GP, it will create a respective user in SQL Server and automatically assign them to the group named DYNGRP.

A user is assigned to the DYNGP group in the DYNAMICS and company databases.

The SQL Server “sa” account is also the administrator of GP.

DYNSA is the assistant to the “sa” user which can do many administrative functions. It also has some limitations as it cannot create new users and you cannot create a new company through DYNSA. So “sa” will be required to do these jobs.

Now in the below sessions, we will show commonly used DYNAMICS table information along with the GP forms that are required in day to day troubleshooting.

In the below session, you might need to run queries through SQL Server management studio, which is by default available in SQL Server menu.

Once the management studio is opened, you need to choose New Query to open a window and run the query. When the query has completed, press F5 to execute it.

Where to find the list of all companies registered in GP through SQL Server

From SQL server the list of GP companies can be retrieved from the Dynamics table named “SY01500” with the following code:

How to reset the System password

The system password can be reset through the following session:

(Tools > Setup > System > System Password)

But in case you forget the GP system password, then it can be re-set through SQL Server with the below query. This password is stored in the table named SY02400.

Where to find GP Users

Dynamics GP users can be maintained through the below menu.

(Tools > Setup > System > User)

The list of users can also be retrieved through the below query in SQL Server:

Understanding User Activity

User activity shows all the users logged into a specific company during a specific date and time.

The form can be accessed via:

Tools > Utilities > System > User Activity

If a user needs to be disconnected, the user can be selected and deleted, as shown below.

 

The above user activity is stored in the DYNAMICS table named “activity”.

They can be viewed through the below query:

To disconnect the user, a DELETE query can be executed for the specific user.

Where is my license/registration key information stored?

The license and registration information can be accessed via:

Tools > Setup > System > Registration

The same information can be accessed through SQL Server from the DYNAMICS table named “SY03500” with the following code:

How to access Currency Master

Currency master is accessed via Tools > Setup > System > Currency.

The same information can be queried from the DYNAMICS table named “MC40200” with the following code:

How to restrict users from logging in

Often GP administrators want users to be restricted from logging in to GP so that they can perform maintenance procedures. This can be achieved through SQL Server.

First, disconnect all the users from GP and change the required company database property option named “Restrict Access” to “SINGLE_USER”.

Below is the screenshot for the property option. Once this is completed, only a single user can log in.

 

We hope these GP tips and tricks have helped you on your way to mastering Microsoft Dynamics GP. Leave your questions and comments below and don’t forget to sign up to our free newsletter for even more Microsoft Dynamics tutorials and tips.

April 23, 2014