0

I'm migrating sql server 2017 to sql server 2019 on a separate machine.

I have backed up all the user databases, scripted all the sql jobs and logins.

On target server I will 1st run the logins script, then restore the user dbs and then the jobs.

How to migrate the permissions of logins - for example - grant/remove/deny permissions on system objects and server instance?

1

3 Answers 3

1
+50

Check out the Microsoft Migration Assistant to handle this task:

https://www.microsoft.com/en-us/download/details.aspx?id=53595

I found it quite valuable when moving from 2017 to 2019.

3
  • The DMA tool takes care of schema, data, database users, permissions (grants/removekes/denys) as well as server roles, custom server roles, permissions.
    – variable
    Apr 8, 2022 at 5:19
  • But I dont think it allows to migrate system object permissions - any idea?
    – variable
    Apr 8, 2022 at 5:20
  • Not on that, no. It was all user objects. Sorry, hope it helps get you closer to your goal though.
    – Silvanarix
    Apr 9, 2022 at 16:25
0

Normally i use this script to have the permissions at instance level:

https://sqlstudies.com/free-scripts/sp_srvpermissions/

2
0

Have you tried https://dbatools.io/? There are cmdlets that can do the job for you. The logins are grabbed as sql management objects and created on target servers in the exact same way they existed on the source server. The command you are looking for is Copy-DBALogin. Check docs here https://docs.dbatools.io/Copy-DbaLogin. Syntax:

Copy-DBALogin -Source $Server1 -Destination $Server2 -Login $Login1 -EnableExecption. 
1
  • Thanks for the edit @mustaccio, I will throw my syntax in code blocks next time! Apr 7, 2022 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.