Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    100

    Red face Unanswered: Problem with droping users, please help

    Hi

    I'm trying to write a script that will make sure the user accounts are set up correctly after de attaching a database moving it to another machine and reattaching it. The problem is the user example1 had a login Name of example1 but after I've copied it over and re attached it the user example1 has No user at all, throught EM all I have to do is Delete the Database user and recreate it correctly, but We need to do this as part on a script, However I don't seem to be able to do the same thing through SQL.

    The way I was attempting to drop and recreate the user was like this:

    Execute sp_dropuser example1
    Execute sp_addlogin example1
    Execute sp_adduser example1
    Execute sp_addrolemember @rolename = db_datareader, @membername = example1
    Execute sp_addrolemember @rolename = db_datawriter, @membername = example1

    However it comes back with this error:

    Server: Msg 15175, Level 16, State 1, Procedure sp_droplogin, Line 93
    Login 'example1' is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login.

    Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 56
    The login 'example1' already exists.

    Does any one now how I can get it too work like the EM version!

    My eternal gratitude, Nixies

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Problem with droping users, please help

    You need to exec sp_grantdbaccess first before you do exec sp_addrolemember.

  3. #3
    Join Date
    Jul 2003
    Posts
    100

    Re: Problem with droping users, please help

    Originally posted by joejcheng
    You need to exec sp_grantdbaccess first before you do exec sp_addrolemember.
    Just doubled checked, dbaccess is fine and the sp_addrolemember went through with out any errors. My big problem is that I can't drop the user to recreate them. Any ideas?

  4. #4
    Join Date
    May 2003
    Location
    Fort Lauderdale
    Posts
    2

    Try This

    I had a similar problem a while back and was able to use the procedures from Microsoft to fix. Check out:

    http://support.microsoft.com/default...;EN-US;298897.

    Basically, you need to resynchronize the login SID's before you can perform any security edits. Hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •