logins in sql server 2005

  • hi , if we transfers logins from server A to server B thr scripts . server A having existing logins or not ...

    before trying ,i have some confusion pls help me

    can any one pls help me

  • Perfect. you are not moving the logins. Just creating the same logins using script i.e copying the logins in another server. Same were existed in A server and B server. You can gear up.

    Regards,

    Naveen:)

  • If you create the same logins, which means you are creating different sql server logins but having the same name as for your initial instance, what will happen when you are trying to restore your user db from the initial server to the 2nd is that you will not have the sql logins (security at the instance level) and users (security at the db level) synchronised, in which case you will have to drop all users and then re-create them from the newly created logins. I cannot say this is funny if we're talking about>25 users. This is because the new logins will be created with different sids (read in BOL). This is the reason why moving logins is preferred, which will keep the same sids.

    It's easy to do this now, please check http://support.microsoft.com/kb/246133, and use Method 2.

    Good luck.

  • I normally use this script:

    SET NOCOUNT ON

    SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''

    ,', @defdb = ''' + dbname + ''''

    ,', @deflanguage = ''' + language + ''''

    ,', @encryptopt = ''skip_encryption'''

    ,', @passwd ='

    , cast(password AS varbinary(256))

    ,', @sid ='

    , sid

    FROM syslogins

    WHERE name NOT IN ('sa')

    AND name NOT LIKE ('##%')

    AND isntname = 0

    Run it on the source server and then run the output on the destination server. You need to either have the default database on the destination server or change the value for defdb though.

    hope this helps

  • What is wrong with sp_change_users_login?

  • Danny Springer (11/22/2007)


    What is wrong with sp_change_users_login?

    Nothing at all. However, if you can script out the logins and recreate them with identical SIDs, that saves you a tremendous amount of work using sp_change_users_login if you have multiple user databases to move/restore.

    K. Brian Kelley
    @kbriankelley

  • K. Brian Kelley (11/22/2007)


    Danny Springer (11/22/2007)


    What is wrong with sp_change_users_login?

    Nothing at all. However, if you can script out the logins and recreate them with identical SIDs, that saves you a tremendous amount of work using sp_change_users_login if you have multiple user databases to move/restore.

    It's also good when you refresh dev environments - instead of having to run sp_change_users_login every time you refresh your dev environment from production, you just create the logins once as per above and every other time you just do a restore. Of course, you may want to change any passwords in dev so they don't match production, but that can also be handled by a variation of the script above.



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


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

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