User already exists! After backup restore

  • Hi,

    I restored a backup in other server, and when I configuered to access the restored database, I receive the msg: Error 15023: User or role 'USER' already exists in the current database.

    Anybody knows how to fix that?

  • Try

    USE dbanme

    GO

    EXEC sp_change_users_login 'Auto_Fix'

    GO

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • what happens is that you restored a database with defined users in it but are not defined under SQL Server's. To see which database users have no corresponding sql server logins, exec sp_change_users_login 'report'.

    To fix, run these:

    use database_name_here

    go

    exec sp_change_users_login 'auto_fix', 'username_you_are_trying_to_fix'

    go

    quote:


    Hi,

    I restored a backup in other server, and when I configuered to access the restored database, I receive the msg: Error 15023: User or role 'USER' already exists in the current database.

    Anybody knows how to fix that?


  • Thank you very very much... I have been looking at this today and could not get an answer from Help, Microsoft or any one else who knows me 🙂

    If you are ever in the UK look up focusdk and I will buy you a beer.

    quote:


    what happens is that you restored a database with defined users in it but are not defined under SQL Server's. To see which database users have no corresponding sql server logins, exec sp_change_users_login 'report'.

    To fix, run these:

    use database_name_here

    go

    exec sp_change_users_login 'auto_fix', 'username_you_are_trying_to_fix'

    go

    quote:


    Hi,

    I restored a backup in other server, and when I configuered to access the restored database, I receive the msg: Error 15023: User or role 'USER' already exists in the current database.

    Anybody knows how to fix that?



    -------------------------

    Your only jealous because the voices are talking to me!!!

    -------------------------


    -------------------------
    Your only jealous because the voices are talking to me!!!
    -------------------------

  • I was also facing this problem for a long time and thanks for this tip. I will try immediately.

    I worked around eachtime as follows(which is not a good way of handling, I guess. But it worked).

    sp_dropuser 'username_you_are_trying_to_fix'

    execute sp_grantdbaccess @loginame='username_you_are_trying_to_fix', @name_in_db='particular_db'

    go

    NeoNash

  • Sp_dropuser will cause you to lose any permissions assigned to the user (should be assigned to the role anyway!). One other note, sp_dropuser has been deprecated by MS, the preferred method is sp_revokedbaccess. If you look in the code sp_dropuser just calls sp_revokedbaccess anyway!

    Andy

  • Ricky,

    You have another beer waiting for you in Oklahoma City, OK!

    Thanks!

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

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