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 try to grant a user access to or remove user access from a company in Microsoft Dynamics GP: "This user cannot be removed from one or more databases" or "Drop failed for User"

Error message when you try to grant a user access to or remove user access from a company in Microsoft Dynamics GP: "This user cannot be removed from one or more databases" or "Drop failed for User"

View products that this article applies to.

Symptoms

When you try to grant a user access to a company in Microsoft Dynamics GP, you receive the following error message:
This user cannot be removed from one or more databases.
When you try to remove user access from a company, you receive the following error message:
Drop failed for User 'user name'.
Additionally, if debugging is enabled in the Dex.ini file, the following information is logged in the Dexsql.log file that is generated:
[Microsoft][ODBC SQL Server Driver][SQL Server]The database principal owns a schema in the database, and cannot be dropped.*/
You experience this problem after you upgrade Microsoft Dynamics GP databases to SQL Server 2005/SQL Server 2008 or if a user owns one or more schemas.

↑ Back to the top

Cause

This problem occurs if the Microsoft Dynamics GP user that you are attempting to remove from the company database owns a schema within that company database

Note A schema is not used for all Microsoft Dynamics GP users.

↑ Back to the top

Resolution

To resolve this problem, follow these steps:
  1. On the computer that is running SQL Server 2005 or SQL Server 2008, click Start, point to Programs, point to Microsoft SQL Server 2005 or Microsoft SQL Server 2008, and then click SQL Server Management Studio.
  2. Log on to SQL Server by using the sa user name together with the corresponding password.
  3. On the File menu, point to New, and then click Database Engine Query.
  4. Download the MassDropUserSchemas.sql script. To do this, visit the following Microsoft Web site:
    https://mbs.microsoft.com/fileexchange/?fileID=d408306e-827d-4510-98ac-f7b48cedc66e
  5. Paste the contents of the script into a blank query window.
  6. On the Query menu, click Execute.
  7. Log on to Microsoft Dynamics GP.
  8. Use the appropriate method:
    • In Microsoft Dynamics GP 10.0 and in Microsoft Dynamics GP 2010, point to Tools on the Microsoft Dynamics GP menu, point to Setup, point to System, and then click User Access.
    • In Microsoft Dynamics GP 9.0, point to Setup on the Tools menu, point to System, and then click User Access.
  9. Grant the user access to a Microsoft Dynamics GP company. Or, remove the user access from the Microsoft Dynamics GP company.

If the same error occurs, there could one or more schemas that are owned by that user. Follow these steps to verify that the user does not own another schema:
  1. Log into the SQL Server Management Studio. To do this, follow the steps one and two of the previous process.
  2. In the Object Explorer window, expand Databases, expand <Database name>, expand Security, and expand Users.
  3. Right-click on the user and then click Properties.
  4. On the first page that appears there are two boxes with items. that can be selected. The items are labeled as Schemas owned by this user and Database role membership. Note the name of the squema.
  5. Click Cancel to close the Properties window.
  6. In the Object Explorer pane, collapse the Users folder and expand the Schemas folder.
  7. Right-click on the schema noted from step 4 and click Properties. The page Schema Properties-<SchemaName> should appear.
  8. Change the Schema Owner from the current user to the new user name. For example, if the schema name is DYNGRP and the owner is different, change the owner name to DYNGRP.
  9. Follow the steps 7-9 of the previous process to remove or give company access for a user.

↑ Back to the top

Keywords: kbmbsgp10,, kbfreshness2007, kbexpertiseinter, kbmbspartner, kberrmsg, kbtshoot, kbentirenet, kbmbsmigrate, kbprb, kb

↑ Back to the top

Article Info
Article ID : 919723
Revision : 1
Created on : 1/7/2017
Published on : 7/14/2012
Exists online : False
Views : 203