Error message when you try to delete a user record in Microsoft Dynamics GP: "DBMS 0 Dynamics 20422 - Stored Procedure smDropUser"

Article ID: 866373 - View products that this article applies to.
Expand all | Collapse all

On This Page

Symptoms

When you try to delete a user record in Microsoft Dynamics GP or in Microsoft Business Solutions - Great Plains 8.0, you receive the following error message:
The stored procedure smDropUser returned the following results: DBMS: 0, Dynamics: 20422.
Additionally, when you click More Info, you receive the following error message:
smDropUser. You have specified to control users through the Dynamics application but the Userid passed into this stored procedure did not exist as either a user or alias in the database.

Cause

This issue may occur when one of the following conditions is true:
  • The corresponding login name in Microsoft SQL Server was deleted. To resolve this issue, go to Method 1 in the "Resolution" section.
  • The user who is being deleted has an entry in the SY60100 table, and there is no matching company ID entry in the SY02100 table. The "smDropUser" stored procedure is not found because that company database name is being passed as a blank. Additionally, an entry for that company was not located in the SY02100 table.

    This issue can occur when databases are deleted by using SQL Enterprise Manager or by using the SQL Management Studio, but not all records are correctly removed from the DYNAMICS tables.

    To resolve this issue, go to Method 2 in the "Resolution" section.

Resolution

Method 1

Follow the steps that are appropriate for your situation.

Microsoft Dynamics GP

  1. Start SQL Server Management Studio, SQL Query Analyzer, or the Support Administrator Console. To do this, use one of the following methods, depending on the program that you are using:
    • If you are using Microsoft SQL Server 2005, start SQL Server Management Studio.

      To do this, click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
    • If you are using Microsoft SQL Server 2000, start SQL Query Analyzer.

      To do this, click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
    • If you are using SQL Server Desktop Engine (also known as MSDE 2000), start the Support Administrator Console.

      To do this, click Start, point to All Programs, point to Microsoft Administrator Console, and then click Support Administrator Console.
  2. Use one of the following methods:
    • If you are using SQL Server 2005, follow these steps:
      1. Open SQL Server Management Studio.
      2. Expand Databases, select the Microsoft Dynamics GP databases, and then click Security.
      3. Delete the user from the Schemas folder and from the Users folder.
      4. Run the following statement against each Microsoft Dynamics GP database.
        DROP SCHEMA <user>
        Note Replace <user> with the login name that you want to delete.
      5. Run the following statement against each database to remove the SQL Server user.
        sp_dropuser <user>
      6. Delete the login name for the user whom you want to delete. To do this, expand Databases, expand the Microsoft Dynamics GP databases, expand Security, and then click Logins.

        Delete the login name that you were trying to remove when you received the error message.
      7. Remove the references for the user in the DYNAMICS database. To do this, run the following statement.
        delete DYNAMICS..SY01400 where USERID = <XXX> delete DYNAMICS..SY60100 where USERID = <XXX>
        Note Replace <XXX> with the appropriate user ID.
    • If you are using MSDE 2000, follow these steps:
      1. Open the Support Administrator Console.
      2. Expand Databases, select the Microsoft Dynamics GP databases, and then click Security.
      3. Run the following statement against each Microsoft Dynamics GP database to remove the SQL Server user.
        sp_dropuser <user>
        Note Replace <user> with the login name that you want to delete.
      4. Delete the login name for the user whom you want to delete. To do this, run the following statement.
        sp_droplogin <login>
        Note Replace <login> with the login name that you want to delete.
      5. Remove the references for the user in the DYNAMICS database. To do this, run the following statement.
        delete DYNAMICS..SY01400 where USERID = <XXX> delete DYNAMICS..SY60100 where USERID = <XXX>
        Note Replace <XXX> with the appropriate user ID.

Microsoft Business Solutions - Great Plains 8.0

  1. Log on to Microsoft Business Solutions - Great Plains 8.0 as the "sa" user.
  2. Click Tools, point to Setup, point to System, and then click SQL Options.
  3. Click to clear the Server Logins check box and the Database Users check box.
  4. Use one of the following methods:
    • If you are using SQL Server 2005, follow these steps:
      1. Open SQL Server Management Studio.
      2. Expand Databases, select the Microsoft Dynamics GP databases, and then click Security.
      3. Delete the user from the Schemas folder and from the Users folder.
      4. Run the following statement against each Microsoft Dynamics GP database.
        DROP SCHEMA <user>
        Note Replace <user> with the login name that you want to delete.
      5. Run the following statement against each database to remove the SQL Server user.
        sp_dropuser <user>
      6. Delete the login name for the user whom you want to delete. To do this, expand Databases, expand the Microsoft Dynamics GP databases, expand Security, and then click Logins.

        Delete the login name that you were trying to remove when you received the error message.
      7. Remove the references for the user in the DYNAMICS database. To do this, run the following statement.
        delete DYNAMICS..SY01400 where USERID = <XXX> delete DYNAMICS..SY60100 where USERID = <XXX>
        Note Replace <XXX> with the appropriate user ID.
    • If you are using MSDE 2000, follow these steps:
      1. Open the Support Administrator Console.
      2. Expand Databases, select the Microsoft Dynamics GP databases, and then click Security.
      3. Run the following statement against each Microsoft Dynamics GP database to remove the SQL Server user.
        sp_dropuser <user>
        Note Replace <user> with the login name that you want to delete.
      4. Delete the login name for the user whom you want to delete. To do this, run the following statement.
        sp_droplogin <login>
        Note Replace <login> with the login name that you want to delete.
      5. Remove the references for the user in the DYNAMICS database. To do this, run the following statement.
        delete DYNAMICS..SY01400 where USERID = <XXX> delete DYNAMICS..SY60100 where USERID = <XXX>
        Note Replace <XXX> with the appropriate user ID.
  5. In Microsoft Business Solutions - Great Plains 8.0, click Tools, point to Setup, point to System, and then click Users.

    Use the lookup to select the user whom you want to delete, and then select the Delete button.
  6. Click Tools, point to Setup, point to System, and then click SQL Options.
  7. In the SQL Options window, click to select the Server Logins check box and the Database Users check box.

Resolution 2

  1. Run the following statement against the DYNAMICS database.
    select distinct(CMPANYID) from SY60100 where CMPANYID not in (select CMPANYID from SY02100)
    If results are returned, the issue is the mismatch between the SY60100 table and the SY02100 table.
  2. To determine which users this issue will affect, run the following statement against the DYNAMICS database.
    select * from SY60100 where CMPANYID not in (select CMPANYID from SY02100)
  3. To correct the issue, back up the DYNAMICS database, and then run the following statement against the DYNAMICS database:
    delete SY60100 where CMPANYID not in (select CMPANYID from SY02100)
    Now you can delete the user by using the eEnterprise application.

Properties

Article ID: 866373 - Last Review: July 13, 2012 - Revision: 6.0
Applies to
  • Microsoft Dynamics GP 10.0
  • Microsoft Dynamics GP 9.0
  • Microsoft Business Solutions–Great Plains 8.0
  • System Manager
Keywords: 
kbtshoot kbprb kberrmsg kbmbsmigrate KB866373

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com