Dynamics GP/SQL Database Login Disconnect

This article deals with the following error messages in Dynamics GP:

• “A get/change first operation on table 'coProcess' failed accessing SQL data”
• “[Microsoft][SQL Native Client][SQL Server]The server principal "LESSONUSER2" is not able to access the database "TWO" under the current security context.”
• “The user could not be added to one or more databases.”
• “A get/change first operation on table 'SY_Users_MSTR' failed accessing SQL data”
• “[Microsoft][SQL Native Client][SQL Server]The server principal "LESSONUSER1" is not able to access the database "DYNAMICS" under the current security context.”
• “[Microsoft][SQL Native Client][SQL Server]EXECUTE permission denied on object 'zDP_SY01400SS_1', database 'DYNAMICS', schema 'dbo'.”

Dynamics GP tracks users separately from SQL Server.  However, there is a one to one relationship between a Dynamics GP user and a SQL Server login.  Both the Dynamics GP user and corresponding SQL Server login have the same username.  Additionally, when a user is granted access to a company, their SQL login is added as a SQL user in that company’s database as part of the DYNGRP role.  The SQL logins associated with Dynamics GP users also have an associated SQL database user in the DYNAMICS database also a member of the DYNGRP role.

Problems happen when the Dynamics GP users and SQL logins get out sync.  There are five situations when these get out of sync:

• The Dynamics GP user has been given access to a company in GP but the corresponding SQL login doesn’t have access to that company.
• The Dynamics GP user doesn’t have access to a company in GP but the corresponding SQL login does have access to the company.
• The Dynamics GP user doesn’t have access to the DYNAMICS database.
• The Dynamics GP user’s corresponding SQL login doesn’t have access to the Dynamics database.
• The Dynamics GP user’s corresponding SQL login isn’t a member of the DYNGRP role in the databases it is supposed to have access to.

First we’ll look at each of these situations individually and how to individually resolve them.  Then we’ll look at a script that will clean up all of them at once.

Dynamics User w/ Missing SQL User

This is the case where a Dynamics GP user has been given access to a company in GP but the corresponding SQL login doesn’t have access to that company.  In this case, the user will receive the following error message when logging into Dynamics GP:

Clicking on “More Info” returns the following:

“[Microsoft][SQL Native Client][SQL Server]The server principal "LESSONUSER2" is not able to access the database "TWO" under the current security context.”

To resolve this error, go into SQL Management Studio and add the corresponding SQL login to the company database as a member of the DYNGRP role.

SQL User in Company Database w/o Corresponding Dynamics GP User Access

In this case, the Dynamics GP user does not have access to a particular company but the corresponding SQL login does have access.  This causes an error in the User Access form if you attempt to add the user to the offending company as follows:

“The user could not be added to one or more databases.”

In this case, going into SQL Management Studio and removing the SQL login’s access to the company database will resolve the error and allow the Dynamics GP user to be given access to the company.

SQL User Doesn’t Have Access to the DYNAMICS Database

In this case, the Dynamics GP user has access to the company database, but doesn’t have access to the DYNAMICS database.  If this happens, the user will see the following error when attempting to login to Dynamics GP.

Clicking on “More Info” returns the following:

“[Microsoft][SQL Native Client][SQL Server]The server principal "LESSONUSER1" is not able to access the database "DYNAMICS" under the current security context.”

To resolve this error, go into SQL Management Studio and add the corresponding SQL login to the DYNAMICS database as a member of the DYNGRP role.

SQL User in Company Database w/o Membership in DYNGRP SQL Database Role

In this case, the Dynamics GP user has access to a company and their corresponding SQL login has access to the company database, however, they are not a member of the DYNGRP role in the company database.  In this case the user will see the following error when trying to login to the company in question:

 

Clicking on “More Info” returns the following:

“[Microsoft][SQL Native Client][SQL Server]EXECUTE permission denied on object 'zDP_SY01300F_1', database 'TWO', schema 'dbo'.”

To resolve this, go into SQL Management Studio and add the corresponding SQL login to the DYNGRP in the company database in question.

SQL User Is Not a Member of the DYNGRP Role in the DYNAMICS Database.

In this case, the SQL user is properly setup in the company database and has access to the DYNAMICS database, but is not a member of the DYNGRP role in the DYNAMICS database.  When logging in in this situation, the user will receive the following error:

Clicking on “More Info” returns the following:

“[Microsoft][SQL Native Client][SQL Server]EXECUTE permission denied on object 'zDP_SY01400SS_1', database 'DYNAMICS', schema 'dbo'.”

To resolve this, go into SQL Management Studio and add the corresponding SQL login to the DYNGRP in the DYNAMICS database.

A Script to Fix it All

So the individual fixes are all well and good, but we recently ran a Reformatter project, and in the process, moving from one SQL server to another, at a client with 50+ users and 230+ companies.  In addition, we were reconfiguring all of their security to take advantage of GP 10’s role based security.  Something we hadn’t done during the original upgrade.

When all was said and done, we ended up with a number of situations where there were various disconnects in the users, mostly SQL logins missing from companies when they should have been there and in companies they shouldn’t.  Fixing all this by hand would have been a nightmare, so we decided to write a script that would fix it.  In this case we had to write a script to write a script (see prior article related to this and how to setup SQL Management Studio to do this).  Here, we not only had to write a script that created a script against each company, but also against each user as follows:

--  Don't forget to set Results to Text in your
--  SQL Management Studio query window.
--
--  Set NOCOUNT on so that row count doesn't show
--  up in the text results.
SET NOCOUNT ON;

--  Common table expression that returns every
--  possible combination of user and company.
WITH UserCompanyCombinations
AS (
    SELECT
            SY01400.USERID
            ,SY01500.CMPANYID
            ,SY01500.INTERID
        FROM SY01400 -- User Master table
            CROSS JOIN SY01500 -- Company Master table
        WHERE SY01400.USERID NOT IN ('sa', 'DYNSA')
)
--  Script to add all users to DYNAMICS database
SELECT
        SQLStatement =
            REPLACE(
'
USE DYNAMICS
GO

PRINT ''ADDING USER {USERNAME} to DYNAMICS database.''

CREATE USER [{USERNAME}] FROM LOGIN [{USERNAME}]
GO

EXEC sp_addrolemember ''DYNGRP'', ''{USERNAME}''
GO

----------

',
            '{USERNAME}', USERID)
    FROM SY01400 -- User Master table
    WHERE USERID NOT IN ('sa', 'DYNSA')

UNION ALL

--  Script to remove users from company databases they shouldn't
--  have access to.

SELECT 
        SQLStatement =
            REPLACE(REPLACE(
'
USE {COMPANYDB}
GO

PRINT ''REMOVING USER {USERNAME} from {COMPANYDB} database.''

DROP USER [{USERNAME}]
GO

----------

',
            '{COMPANYDB}', UserCompanyCombinations.INTERID)
            , '{USERNAME}', UserCompanyCombinations.USERID)
    FROM UserCompanyCombinations
        LEFT JOIN SY60100 -- User Access table
            ON UserCompanyCombinations.USERID = SY60100.USERID
                AND UserCompanyCombinations.CMPANYID = SY60100.CMPANYID
    WHERE SY60100.CMPANYID IS NULL

UNION ALL

--  Script to add users to company databases they should have access to
SELECT
        SQLStatement =
            REPLACE(REPLACE(
'
USE {COMPANYDB}
GO

PRINT ''ADDING USER {USERNAME} to database {COMPANYDB}''

CREATE USER [{USERNAME}] FROM LOGIN [{USERNAME}]
GO

EXEC sp_addrolemember ''DYNGRP'', ''{USERNAME}''
GO

----------

',
            '{COMPANYDB}', SY01500.INTERID)
            , '{USERNAME}', SY60100.USERID)
    FROM SY01500 -- Company Master table
        INNER JOIN SY60100 -- User Access table
            ON SY01500.CMPANYID = SY60100.CMPANYID
    WHERE SY60100.USERID NOT IN ('sa', 'DYNSA')

As you can see this is a union of three separate queries as follows:

• The first query creates a SQL statement for each user, adds them to the DYNAMICS database and then makes them a member of the DYNGRP role. 
• The second query creates a SQL statement for each user and company that the Dynamics GP user doesn’t have access to a company as defined by the SY60100 user access table and removes their SQL user from the associated company database.
• The final query creates a SQL statement for each user and company database that the Dynamics GP users do have access to as defined by the SY60100 user access table, adding the corresponding SQL login to the database as a member of the DYNGRP role.

You’ll note that in all of the queries we’re excluding the “sa” and “DYNSA” users as these are treated as special users in Dynamics GP.  “sa” should always be in the “sysadmin” server role, giving it full access to all databases.  The “DYNSA” user maps to the “dbo” (database owner) user in each database rather than a user of the same name.  This gives the “DYNSA” user special privileges in the database, such as the ability to modify database objects.

When you run the script above you should receive something like the following in the results pane:

USE DYNAMICS
GO

PRINT 'ADDING USER LESSONUSER1 to DYNAMICS database.'

CREATE USER [LESSONUSER1] FROM LOGIN [LESSONUSER1]
GO

EXEC sp_addrolemember 'DYNGRP', 'LESSONUSER1'
GO

----------

USE DYNAMICS
GO

PRINT 'ADDING USER LESSONUSER2 to DYNAMICS database.'

CREATE USER [LESSONUSER2] FROM LOGIN [LESSONUSER2]
GO

EXEC sp_addrolemember 'DYNGRP', 'LESSONUSER2'
GO

----------

USE FOUR
GO

PRINT 'REMOVING USER LESSONUSER2 from FOUR database.'

DROP USER [LESSONUSER2]
GO

----------

USE TWO
GO

PRINT 'ADDING USER LESSONUSER1 to database TWO'

CREATE USER [LESSONUSER1] FROM LOGIN [LESSONUSER1]
GO

EXEC sp_addrolemember 'DYNGRP', 'LESSONUSER1'
GO

----------

USE TWO
GO

PRINT 'ADDING USER LESSONUSER2 to database TWO'

CREATE USER [LESSONUSER2] FROM LOGIN [LESSONUSER2]
GO

EXEC sp_addrolemember 'DYNGRP', 'LESSONUSER2'
GO

----------

USE FOUR
GO

PRINT 'ADDING USER LESSONUSER1 to database FOUR'

CREATE USER [LESSONUSER1] FROM LOGIN [LESSONUSER1]
GO

EXEC sp_addrolemember 'DYNGRP', 'LESSONUSER1'
GO

----------

Note that this is a little quick and dirty and running this script will cause errors when it tries to create users that already exist or drop users that don’t.  Adding some extra code to check for a user’s existence or not wouldn’t be that difficult.