Results 1 to 3 of 3

Thread: database user problem when restoring

  1. #1
    prasad Guest

    database user problem when restoring

    Hi everybody

    i am taking the full database backup on server sql1 and restoring on sql2. there are some logins in sql1 which are not in sql2. i restored the database ,and try to create the login with same name as in sql1 and try to create the user in the database ,its giving me the error the user is already in the database . when i checked the users the user is not in the database ,but he is in the public role .i try to remove from the role its not doing it..

    some body please tell me fix for this broblem

    ThanKs prasad


  2. #2
    James May Guest

    database user problem when restoring (reply)

    I'm in a hurry, so I can't give you a comprehensive answer, but the problem is that the db you restored still has users in the dbs SysUsers table -- even though you created new logins with the same names, SQL Server needs to have the SID from Master..SysLogins to match the SID in YourDB..SysUsers. You can take a quick look at the SIDs for your users in Master..SysLogins and compare them to YourDB..SysUsers and see what I mean. There are many ways to fix this, the best one being dependent on how many users there are and how often you do this restore. You might want to do an UPDATE to YourDB..SysLogins. Or you might want to take advantage of the @SID parameter of sp_AddLogin if you're going to be doing this restore frequently -- that way you can synch up the SIDs in the SysLogins on both servers. But considering I don't know these details nor how comfortable you are with updating system tables, one way to do this is to:

    SELECT * FROM YourDB..SysUsers

    then VERY CAREFULLY

    DELETE YourDB..SysUsers WHERE Name
    IN ('FirstUserName' , 'SecondUserName' , 'nthUserName&#39
    Oh, yes, to delete from system tables you'll have to run

    sp_Configure 'allow updates' , TRUE
    RECONFIGURE WITH OVERRIDE

    before you whack the users, then run this again using FALSE afterward.

    Then, as long as the users exist as server logins, you'll find you'll be able to add the users to the db as you normally would. If you use Enterprise Manager to add users, don't forget to refresh when necessary.


    ------------
    prasad at 3/14/01 5:49:35 PM

    Hi everybody

    i am taking the full database backup on server sql1 and restoring on sql2. there are some logins in sql1 which are not in sql2. i restored the database ,and try to create the login with same name as in sql1 and try to create the user in the database ,its giving me the error the user is already in the database . when i checked the users the user is not in the database ,but he is in the public role .i try to remove from the role its not doing it..

    some body please tell me fix for this broblem

    ThanKs prasad


  3. #3
    Dale Shaw Guest

    database user problem when restoring (reply)

    Hi

    I have this happen all the time and I have written a stored procedure that will call SP_DROPUSER then SP_ADDUSER and SP_ADDROLEMEMBER to rebuild the correct users<->login connections.


    Dale




Posting Permissions

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