FW: 如何移轉SQL Server Login/ Role/ Job 到其他資料庫 (Apply to SQL 2000 to 2016)

除了以往的SP_help_revlogin, 現在有新的powershell去做這件事

 

Copy-SqlLogin 和 Export-SqlLogin 是針對這個工作的powershell cmdlet

 

而最可貴的是他從Sql2000到sql 2016都有支援到, 大大簡化了流程的複雜度

 

1.

#export all of your logins to a file, complete with SID, hashed password, roles, permission sets, securables

Export-SqlLogin -SqlServer sql2005 -FileName C:\temp\sql2005-logins.sql 

 

2.

#To Live login migration from SQL Server 2000 to SQL Server 2016

Copy-SqlLogin -Source sqlsvr2000 -Destination newsql2016 

 

3.

#Syncs the server & db permission sets, as well as the server & db roles and job ownership.

Sync-SqlLoginPermissions -Source sql2005 -Destination sql2016 

 

ref:

https://social.technet.microsoft.com/Forums/zh-TW/22d9b985-101f-4cad-b0e1-22d4d51e1fb5?forum=sqlservermanagementzhcht

https://blog.netnerds.net/2016/06/its-2016-why-is-sp_help_revlogin-a-thing/