Database Validation is a tool designed to ensure that the user, company and table settings at the SQL Server level match what the Microsoft Dynamics GP application says they should be.
Database Validation does not replace any of the tools available already from Microsoft. It performs different functions to Dynamics Utilities, DB Maintenance, or Check Links.
Database Validation ensures that the user, company and access information in the application tables matches the logins, databases and database users in SQL Server. It checks for the correct database owner and permission settings for users. It also checks for invalid data in the Dynamics GP Utilities version tables and checks that the account framework settings match between the application, system database and company databases.
Database Validation can also be used to identify SQL tables and views which don’t appear to be used by any of the installed product dictionaries as well as Dexterity table definitions for which the SQL table or view has not been created. For tables that exist in both the Dexterity product dictionaries and in SQL, Database Validation can compare the tables structures to ensure that the columns and datatypes match.
When Database Validation is opened it performs the following checks on the system.
Users and Databases:
- Confirm that the ‘DYNSA’ SQL Server Login exists
- Confirm that ‘DYNSA’ is assigned as dbo for the SQL Databases
- Confirm that the ‘DYNSA’ GP User ID exists
- Confirm that ‘DYNSA’ is assigned access to all GP Companies
- Confirm that ‘sa’ is assigned access to all GP Companies
- Identify GP Companies for which there is no SQL Database
- Identify GP User IDs for which there is no SQL Login
- Identify GP Users not assigned to the DYNGRP SQL Role
- Identify Company Access records for missing Users or Companies
- Identify missing Database Users as per Company Access records
- Identify Database Users with invalid Security ID (SID) values
Dynamics GP Utilities:
Using the records in DB_Upgrade and DU00020 tables in the System database.
- Identify records for companies that are not installed
- Identify records for product dictionaries that are not installed
- Identify Account Framework from Application Dictionary
- Identify Account Framework from setup tables in System Database
- Identify Account Framework from GL_Account_MSTR (GL00100) table in each Company Database
This information is then displayed in the top half of the window. The red crosses and yellow exclamation marks highlight the various issues found.
After running the Fix Users, Fix Utilities and Fix Framework processes to fix any issues found during the initial checks, you can click Process to read the dictionaries and compare the tables and views identified against the data in the SQL databases. Then you can click Validate to compare the column and datatype structures for the tables existing in both the product dictionaries and the SQL databases.
If any tables are found with incorrect structures, the details are available as a report or via a window to show you the differences.
You can then click Fix Tables. Database Validation provides options to fix the tables depending on whether they contain data and if Dynamics Utilities has a conversion script available.
Examples of Use
Here are some common examples of how using Database Validation makes your life easier:
- Before upgrading you can ensure that there are no invalid users or companies in your system, that Dynamics Utilities has no “junk” in its tables, that the account framework settings match everywhere and that all the tables have the correct column structure and security permissions. This will help ensure a smooth upgrade without errors.
- When transferring all the databases to a new server, GP Power Tools can recreate all the logins and database users including using the application level encryption and marking the passwords to be changed on next login.
- When creating test environments, GP Power Tools can remove all the references to the users and databases that will not be used in the test environment.
For more information on using Database Validation, please review the section in the GP Power Tools User Guide manual PDF, which includes a step by step guide to usage. The manual is installed with the product and is also available as a separate download.
This article was originally posted on http://www.winthropdc.com/blog.