Post reply

logins after db restoration

  • April 28, 2004 at 5:14 am

    #84993

    after restoration of db ,login does not come bcz of server base so how to link logins with the users.

    so that their rights can be maintained after restoration of db.

    thanks

  • April 28, 2004 at 6:50 am

    #504485

    If you restored into the same server, you shoudn't have any problems with the login, unless you have restored the master db.

     

     

  • April 28, 2004 at 7:46 am

    #504499

    ya but that is a diff server,and if i create that login it says user already available ,if i delete the user then all the rights  of that user also lost on different objects.

  • April 28, 2004 at 7:59 am

    #504507

    On the server that you are restoring to drop and recreate the logins with the sids matching the login sid on the first server, that way you won't run into this problem

    select name, sid from master..syslogins


    Shamless self promotion - read my blog http://sirsql.net

  • April 28, 2004 at 9:39 am

    #504530

    go

    EXEC sp_change_users_login 'Update_One', 'Mary', 'NewMary'

     

    This command above will fix your problem if you restore your database to different server, you still need to make sure the login exist on the new server before you could issue this command.

     

    mom

  • April 29, 2004 at 9:25 am

    #504699

    I sometimes use the following, provided the users are already a part of the Security\login:

    (change the LoginName to the user who needs to be changed)

    EXECUTE  sp_change_users_login  'Auto_Fix', 'LoginName'

     

     

  • April 29, 2004 at 1:24 pm

    #504738

    Here's some code that will build the code to resynch the users after you restore.  Just run it from the restored database.

    select 'exec sp_change_users_login @action=''update_one'',@loginname=''' +

     name + ''',@usernamepattern=''' + name + ''''

    from sysusers

    where uid > 2 and uid < 16000

  • May 3, 2004 at 10:26 am

    #505023

    You might want to check out the following links from Microsoft about moving objects from one server to another.  I have used this before (and I'm actually in the middle of a move right now) and it works really good.

    HOW TO: Transfer Logins and Passwords Between Instances of SQL Server

    http://support.microsoft.com/default.aspx?kbid=246133

    HOW TO: Move Databases Between Computers That Are Running SQL Server

    http://support.microsoft.com/default.aspx?kbid=314546

    Good luck!

     

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

    01010011010100010100110000100000010100110110010101110010011101100110010101110010
    001000000101001001101111011000110110101101110011

  • May 5, 2004 at 10:36 am

    #505339

    hawg,

    I have a question? I try those scripts to transfer user login and password between 2 SQL 2000 servers.  I can not get password for each user.  There are blank at password column of primary server. I am sure each user have their owner password.  Is those scripts just work at SQL server 7?

    Thanks,

    KT

     

     

  • May 5, 2004 at 1:19 pm

    #505362

    Are the logins that you are not getting passwords for Windows accounts.  If so, these types of logins do not have passwords, only SQL logins?

    I use this for moving from SQL 2K to SQL 2K.

    I apologize for not completely understanding your question.  Give me some more information if my reply does not answer your question.

    hawg

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

    01010011010100010100110000100000010100110110010101110010011101100110010101110010
    001000000101001001101111011000110110101101110011

  • May 6, 2004 at 9:16 am

    #505498

    hawg,

    Sorry, I am talking about SQL 2K to SQL 2K.  I just retry it again. It works now. 

    Thanks.

    KT

     

     

Viewing 11 posts - 1 through 10 (of 10 total)

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