Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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.
Normally i use this script to have the permissions at instance level:
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.
