SQL Server Performance

Error 21002: [SQL-DMO] User already exists

Discussion in 'General DBA Questions' started by fletcherdunton, Sep 7, 2006.

  1. fletcherdunton New Member

    Here's a basic problem that I should know the answer to, but need help:

    I cloned a database back from our DEV server to my local server and it had 1 user account called "webuser". That account had execute permissions on about 120 stored proc's.

    When I try to create this user under my LOCAL Server's SecurityLogin folder, I get the following error: "Error 21002: [SQL-DMO] User 'webuser' already exists."

    So I go to my databaseUser folder and try and delete the account and it tells me "The Selected User Cannot be dropped b/c it owns objects".

    So I have to right click on it, hit the permissions button and uncheck all 120 exec priv's on the SP's and then delete it. <click><click><click>....ZZZZZzzzzzz [xx(]

    Is there a more efficient way to do this?

    Thank you,

    Fletcher Dunton
    MCAD, MCP
  2. Mast_dba New Member

    Is this solve your problem ? Because this error generally come when some objects are created under user schema. I think ur user must have some object in his schema. Becuase only because of permission u should not get this error "The Selected User Cannot be dropped b/c it owns objects".

    Thanks,

    MAST
    ITS IMPOSSIBLE TO DEFEAT A PERSON WHO NEVER GIVES UP.
  3. satya Moderator

    How about checking for orphaned users run SP_CHANGE_USERS_LOGIN 'REPORT' for such information and this happens due to the mismatch of login id between master and user database, check Books online for more information.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  4. fletcherdunton New Member

    Ok, thank you. This works nice. Here's what I finally came up with:

    1-Use sp_addlogin to create the webuser login with an appropriate password.
    2-Next, logged in with a sysadmin account, connect to the cloned database and issue the following command: sp_change_users_login 'Update_One', 'webuser', 'webuser'
    3-After that's done, you can go back to the login properties and set the default database for webuser to your cloned database.

    Thanks again!


    Fletcher Dunton
    MCAD, MCP

Share This Page