Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Error message when you start Microsoft Dynamics GP: "A Get/Change First operation on table SY_Users_MSTR failed accessing SQL data"

Error message when you start Microsoft Dynamics GP: "A Get/Change First operation on table SY_Users_MSTR failed accessing SQL data"

Symptoms

When you start Microsoft Dynamics GP or Microsoft Business Solutions - Great Plains, you receive the following error message:
A Get/Change First operation on table SY_Users_MSTR failed accessing SQL data

↑ Back to the top

Resolution

To resolve this problem, follow these steps:
  1. Verify that you are using the correct Open Database Connectivity (ODBC) system data source name (DSN). To do this, follow these steps:
    1. In Control Panel, open Administrative Tools.
    2. Double-click Data Sources (ODBC).

    3. Click the System DSN tab.

    4. Click the name of the ODBC data source that you use for Microsoft Dynamics GP or for Microsoft Business Solutions - Great Plains.

    5. Click Configure.



      The server in the Server field is the computer on which the Microsoft Dynamics GP databases or the Microsoft Business Solutions - Great Plains databases reside. Compare this server to the server in the Server field on another workstation on which no problem occurs.
  2. Set up a new ODBC DSN.


    For more information about how to set up a new ODBC DSN, click the following article number to view the article in the Microsoft Knowledge Base:

    870416 ODBC setup on SQL Server 2005, on SQL Server 2000, and on SQL Server Desktop Engine 2000 (MSDE)

  3. Verify theversion of the ODBC. To do this, follow these steps:
    1. In Control Panel, open Administrative Tools.
    2. Double-click Data Sources (ODBC).

    3. Click the System DSN tab.

    4. Click Add.
    5. At the bottom of the Name column, click SQL Server, or click SQL Native Client.

      The versions are displayed in the Version column.
  4. Verify that the DYNSA user is the owner of the DYNAMICS database and of the company databases.

    If the DYNSA user is not the owner of the DYNAMICS database and of the company databases, change the owner of the databases to the DYNSA user. To do this, use one of the following methods:
    • If you use Microsoft SQL Server Enterprise Manager, follow these steps:

      1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.
      2. In the list, click the server that is running SQL Server, and then log on by using the sa user account.

      3. Verify that the DYNSA user is the owner of the DYNAMICS database and of the company databases. To do this, run the following script in the Query box. 
        sp_helpdb
      4. If the values in the Owner field for the DYNAMICS database or for the company databases are not DYNSA, run the following script against the DYNAMICS database and against the company databases. 
        sp_changedbowner 'DYNSA'
    • If you use SQL Server Management Studio, follow these steps:
      1. Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

      2. In the Server name list, click the server that is running SQL Server, and then log on by using the sa user account.

      3. Verify that the DYNSA user is the owner of the DYNAMICS database and of the company databases. To do this, click New Query, and then run the following script in the Query box. 
        sp_helpdb
      4. If the values in the Owner field for the DYNAMICS database or for the company databases are not DYNSA, run the following script against the DYNAMICS database and against the company databases. 
        sp_changedbowner 'DYNSA'
    • If you use Microsoft Support Administrator Console, follow these steps:
      1. Click Start, point to Programs, point to Microsoft Support Administrator Console, and then click Support Administrator Console.

      2. In the SQL Server list, click the server that is running SQL Server, and then log on by using the sa user account.
      3. Verify that the DYNSA user is the owner of the DYNAMICS database and of the company databases. To do this, run the following script in the Query box. 
        sp_helpdb
      4. If the values in the Owner field for the DYNAMICS database or for the company databases are not DYNSA, run the following script against the DYNAMICS database and against the company databases. 
        sp_changedbowner 'DYNSA'
    Notes
    • Do not run the script against the following default SQL Server databases:
      • The master database
      • The model database
      • The tempdb database
      • The msdb database
    • After you change the owner of the databases to the DYNSA user, you should run the Grant.sql script against the DYNAMICS database and against the company databases. The content of the Grant.sql script is as follows. 
      /*Count : 1 */

      declare @cStatement varchar(255)

      declare G_cursor CURSOR for select 'grant select,update,insert,delete on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects 
      where (type = 'U' or type = 'V') and uid = 1

      set nocount on
      OPEN G_cursor
      FETCH NEXT FROM G_cursor INTO @cStatement 
      WHILE (@@FETCH_STATUS <> -1)
      begin
      EXEC (@cStatement)
      FETCH NEXT FROM G_cursor INTO @cStatement 
      end
      DEALLOCATE G_cursor

      declare G_cursor CURSOR for select 'grant execute on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects 
      where type = 'P'  

      set nocount on
      OPEN G_cursor
      FETCH NEXT FROM G_cursor INTO @cStatement 
      WHILE (@@FETCH_STATUS <> -1)
      begin
      EXEC (@cStatement)
      FETCH NEXT FROM G_cursor INTO @cStatement 
      end
      DEALLOCATE G_cursor
      Therefore, appropriate permissions are assigned to the users who have the DYNGRP role. Then, these users can access the Microsoft Dynamics GP objects or the Microsoft Business Solutions - Great Plains objects.

  5. Verify that the user who received the error message that is mentioned in the "Symptoms" section is a user in the DYNAMICS database and in the company databases. To do this, use one of the following methods:
    • If you use SQL Server Enterprise Manager, follow these steps:
      1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

      2. Expand Microsoft SQL Servers, expand SQL Server Group, and then expand the instance of SQL Server.

      3. Expand Security, and then click Logins.

      4. Right-click the user who received the error message, and then click Properties.
      5. Click the Database Access tab, and then verify that the Permit check box is selected for the DYNAMICS database and for the company databases.
      6. For each database to which the Microsoft Dynamics GP user or the Microsoft Business Solutions - Great Plains user has access, make sure that the public check box and the DYNGRP check box are selected in the Permit in Database Role pane.
      7. To save the changes, click OK.
    • If you use SQL Server Management Studio, follow these steps:
      1. Click Start, point to Programs, point to SQL Server 2005 or to SQL Server 2008, and then click SQL Server Management Studio.

      2. Expand Security, and then expand Logins.
      3. Right-click the user who received the error message, and then click Properties.
      4. In the Login Properties dialog box, click User Mapping, and then verify that the map check box is selected for the DYNAMICS database and for the company databases to which the user has access.
      5. For each database to which the Microsoft Dynamics GP user or the Microsoft Business Solutions - Great Plains user has access, make sure that the public check box and the DYNGRP check box are selected in the Database role membership for pane.
      6. To save the changes, click OK.
    • If you use Support Administrator Console, follow these steps:
      1. Click Start, point to Programs, point to Microsoft Support Administrator Console, and then click Support Administrator Console.
      2. Log on by using the sa user account.
      3. In the database list, click the database of the company in which the user received the error message.
      4. Run the following script. 
        EXEC sp_addrolemember 'DYNGRP','<username>'
        Note In this script, <username> is a placeholder for the actual user name.

↑ Back to the top

More Information

Microsoft Dynamics GP or Microsoft Business Solutions - Great Plains must have the owner of databases set to DYNSA to apply the correct permissions for the users. All users are members of the DYNGRP database role. The Grant.sql script gives the users the correct permissions to access the databases.

↑ Back to the top

Keywords: kbprb, kbtshoot, kbexpertiseinter, kbexpertisebeginner, kberrmsg, kbmbspartner, kbmbsmigrate, kb

↑ Back to the top

Article Info
Article ID : 860468
Revision : 1
Created on : 1/7/2017
Published on : 7/18/2012
Exists online : False
Views : 103