Post reply

Cant drop user - Help!!

  • June 22, 2004 at 10:58 am

    #162046

    Hello All,

    I'm at my witts end trying to figure out why I can't drop/add a particular user/login.

    I just recently copied my production database over to my test database.  Everything worked out perfectly except for one user.  I can add and delete the login all day without error.  But when I go to add the user to the test database it keeps giving me User or role 'user_name' already exists in current database.  Both the users and the roles have no such login anywhere in the current database.  I even tried to use the sp_dropuser and sp_droplogin stored procs to try and get rid of it.  Still nothing.

    Does anyone have any suggesstions?  Just FYI, I even got on the Microsoft Knowledge base and used the script 'sp_help_revlogin' to transfer the logins between databases.

    Thanks for your help in advance

    Shane Stecher

  • June 22, 2004 at 11:53 am

    #511490

    It is because this user does not exist in this server. First of all the user must have a login that exists. You can create one if you are a sysadmin. Then you have to associate the login with the database user, because at this point it is an orphan and SQL Server does not know what to do with it. Here you use "exec sp_change_users_login 'Update_one', 'userid', 'userid'". This will reassociate the user id to the database.

  • June 22, 2004 at 12:49 pm

    #511500

    I have run into this in Enterprise Manager.  In some cases you will need to delete the user from the SysUsers table in the problem DB manually with "Delete From SysUser Where Name = '<login name>'". In order to do this you must turn on the Allow Modifications directly to System catalogs option while you are running the delete.

    When this is an EM problem though, and refreshing the DB doesn't work, I have had to exit from EM and restart it, then everything worked OK. EM has a bad habit of caching things it shouldn't.

    Chris

  • June 22, 2004 at 1:45 pm

    #511508

    Make sure the user isn't defined as an owner of any objects in the database.  If so, execute sp_changeobjectowner 'object_name', 'new_owner' to change the owner to a valid database user, then try again.

    Steve

  • June 22, 2004 at 1:47 pm

    #511510

    Look in the Books OnLine for Orphaned Users. I believe the command you want is sp_revokelogin.

    -SQLBill

  • June 22, 2004 at 1:51 pm

    #511511

    Whew!

    Thanks for all the help guys.  After running the sp_change_users_login stored proc than closing and re-opening EM everything seems to be working just fine.

    Thanks again for all the help

  • June 23, 2004 at 5:51 am

    #511601

    Use <databasename>

    sp_change_users_login 'auto_fix', '<username>'

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply