Post reply

Upgrade to SQL 2017 new server- how to copy over logins & Jobs during upgrade

  • November 5, 2019 at 10:12 am

    #3694639

    sdfhsdf

  • November 5, 2019 at 10:30 am

    #3695057

    You can use sp_help_revlogin for all your logins and then script out the jobs.

    Thanks

  • November 5, 2019 at 4:56 pm

    #3695195

    Ok thanks

    I am a bit rusty with this stuff so just to confirm is the correct process as follows:

    • run sp_help_revlogin on the master DB and that will create a script to create all the logins both windows authenticated and sql authenticated on the other server?  will that create the logins with the correct passwords? how is the orphan login issue resolved?  so does the script create both the logins at sql instance level and the users in the database ?
    •  for the jobs - should I right click on each and every job and use the "script as" to create a script and then run on the new server? or is there a quicker method?

    Thanks in advance

  • November 5, 2019 at 6:43 pm

    #3695216

    sp_help_revlogin creates a script that can be run on the other server to create the logins.  Be aware that it scripts out *all* logins and will attempt to create those logins on the new server.  You need to review the list and eliminate any logins that you don't want - especially those logins that are specific to that instance or already exist on the new instance.

    sp_help_revlogin will create the logins with the same SID and password for SQL logins (for Windows logins this doesn't matter).  Because the login is created with the same SID - the user in the database will synch with the login.  The user is created in the database - so when that database is restored on the new instance - all users will synch to the login with the same SID on the new instance.

    To move agent jobs - select the jobs folder and open Object Explorer Details.  In that window - ctrl-select all jobs you want to script then right-click and script job as...again, only copy jobs that are not specific to that instance and don't script the syspolicy or maintenance plan jobs.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • November 7, 2019 at 9:47 am

    #3695831

    Hi,

    just take a look to dbatools.io.

    With a few commands you are able to copy database, jobs and logins from server a to server b.

    It is realy simple.

    Kind regards,

    Andreas

  • November 8, 2019 at 3:43 pm

    #3696284

    Thanks Jeffrey

  • November 8, 2019 at 3:45 pm

    #3696288

    Ok thanks Andreas, that involves powershell to copy the database and jobs and users I take it?

    What would be the command ?

     

  • November 11, 2019 at 2:22 pm

    #3696736

    dbatools has functions to copy pretty much everything. For your particular scenario, you'll be interested in Copy-DbaDatabase (allows you to copy via backup/restore, detach/attach, or by restoring the last backup from the source system), Copy-DbaLogin, and Copy-DbaAgentJob.

    Or, if you're looking to migrate the entire instance, including your DBMail configs, Resource Governor, operators, and literally everything else, check out Start-DbaMigration.

    You may want to do some filtering on each to eliminate anything you don't need. A migration is a great opportunity to clear out cruft.

  • November 11, 2019 at 4:04 pm

    #3696769

    Ok great thanks for the info, I am transferring to different windows domains so I'm not its going to work as I need to use different accounts on different domains.

  • November 11, 2019 at 4:12 pm

    #3696772

    Hi Jeffrey

    Thanks for that, I used sp_help_revlogin and it worked well.

    • It gave me a list of the entire logins on the sql instance.
    •  I then copied out the 5 logins I needed to recreate and ran the script on the new isntance.
    •  I had to then individually add the permissions for the users on the newly copied database.

    My concern would be if I had 50 or 100 logins to modify permissions on the newly restored database how would I do that quickly? so the logins are created but how does one then grant the access to the database to those individual logins i.e read / write , dbo depending on what permissions they had on the old database.

     

  • November 11, 2019 at 4:21 pm

    #3696775

    All you need to do in the database is associate the user with the login.  The permissions come over with the database so you don't need to touch them.  From memory, the command is this:

    USE myDB;

    ALTER USER MyUser WITH LOGIN = MyLogin;

    Usually MyUser and MyLogin will have the same name.

    John

  • November 11, 2019 at 4:42 pm

    #3696789

    Thanks John

    So when copying over the database does that actually bring the permissions of the users over as well? and then after that login is created I need to then run the following to ensure the user is associated with the login and has the correct permissions as it previously did on the old database.

    USE myDB;

    ALTER USER MyUser WITH LOGIN = MyLogin;

  • November 11, 2019 at 4:45 pm

    #3696790

    Yes, that's right.  That applies to database-level permissions.  If there are any server-level permissions (and let's hope there aren't), you'll need to script those out and add them on the new server.

    John

  • November 11, 2019 at 6:41 pm

    #3696830

    Logins and users have an assigned SID - if they are SQL logins/users it is assigned by that instance, if it is a windows login it is assigned by AD (or the windows server if using local windows accounts).

    Because these are identified by SID - when you restore a database to a new instance and the login with the same SID already exists it will match and that login/user will work the same as it did on the previous instance.  The procedure sp_help_revlogin includes the SID and original password for the login - and any users in any databases from that instance will match and nothing further needs to be done to synch the users to the logins.

    If you create the login on the new instance - then it will have a different SID and it will not match, which is when you have to use something like sp_change_users_login.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • November 12, 2019 at 9:14 am

    #3696958

    Thanks for providing the valuable information here!

Viewing 15 posts - 1 through 15 (of 16 total)

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