Post reply

Migrate all user logins

  • January 20, 2019 at 7:20 pm

    #382601

    Hi Guys

    I need to migrate all user logins ( under security folder in SSMS )  from 1 server to another but my problem is the script that I use will remove the sys admin role in destination server so I need to add it back manually .

    if I have a lot of sys admin logins that would be a pain.

    Do you know how to deal with it or any better script that you can share please

    Thanks so much !
    Appreciate your feedback

  • January 21, 2019 at 12:50 am

    #2019492

    What script are you using to migrate the logins? If you're using sp_help_revlogin it should retain the sysadmin permissions.

    Thanks

  • January 21, 2019 at 2:05 am

    #2019498

    Use the sys.server_role_members (that may or may not be the exact name - I can't remember) catalog view to script out the members of the sysadmin server.

    John

  • January 21, 2019 at 8:35 am

    #2019527

    John Mitchell-245523 - Monday, January 21, 2019 2:05 AM

    Use the sys.server_role_members (that may or may not be the exact name - I can't remember) catalog view to script out the members of the sysadmin server.

    John

    This will get you going in the right direction:

    SELECT sprole.name, spmember.name
    FROM sys.server_role_members srm
    INNER JOIN sys.server_principals sprole ON srm.role_principal_id = sprole.principal_id
    INNER JOIN sys.server_principals spmember ON srm.member_principal_id = spmember.principal_id

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • January 21, 2019 at 2:30 pm

    #2019561

    i've been using a modified version of this script sp_help_revlogin_roles, which scripts logins and some server roles for you for an easier migration:

    https://www.itprotoday.com/strategy/cloning-security-objects-between-servers

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 14, 2019 at 9:52 pm

    #2022122

    thanks guy 🙂

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

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