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 at 5:19
  • But I dont think it allows to migrate system object permissions - any idea?
    – variable
    Apr 8 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 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 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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