Skip to content

Instantly share code, notes, and snippets.

@alevyinroc

alevyinroc/Copy-DBWithPermissionsSaved1.ps1

Last active July 19, 2020 11:55
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save alevyinroc/e6aea654ef830c466b1fd23d04438a69 to your computer and use it in GitHub Desktop.
Save alevyinroc/e6aea654ef830c466b1fd23d04438a69 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Copy-DBWithPermissionsSaved1.ps1
import-module dbatools;
$SrcInstance = "flexo\sql17";
$DstInstance = "flexo\sql19";
$UserPassword = 'P@$$w0rd' | ConvertTo-SecureString -AsPlainText -Force;
$DBName = "UserPermTest";
$DstUserScriptPath = "C:\sql\Backup\Sql19Users.sql";
# Create database & users on each
Remove-DbaDatabase -SqlInstance $SrcInstance, $DstInstance -Database $DBName -Confirm:$false | Out-Null;
New-DbaDatabase -SqlInstance $SrcInstance, $DstInstance -Name $DBName -Confirm:$false | Out-Null;
Remove-DbaLogin -SqlInstance $SrcInstance -Login Sql17User -Confirm:$false | Out-Null;
Remove-DbaLogin -SqlInstance $DstInstance -Login Sql19User -Confirm:$false | Out-Null;
Remove-DbaLogin -SqlInstance $DstInstance -Login Sql19User2 -Confirm:$false | Out-Null;
New-DbaLogin -SqlInstance $SrcInstance -Login Sql17User -SecurePassword $UserPassword -PasswordPolicyEnforced:$False -PasswordExpirationEnabled:$False | Out-Null;
New-DbaLogin -SqlInstance $DstInstance -Login Sql19User -SecurePassword $UserPassword -PasswordPolicyEnforced:$False -PasswordExpirationEnabled:$False | Out-Null;
New-DbaLogin -SqlInstance $DstInstance -Login Sql19User2 -SecurePassword $UserPassword -PasswordPolicyEnforced:$False -PasswordExpirationEnabled:$False | Out-Null;
New-DbaDbUser -SqlInstance $SrcInstance -Login Sql17User -Database $DBName | Out-Null;
New-DbaDbUser -SqlInstance $DstInstance -Login Sql19User -Database $DBName | Out-Null;
New-DbaDbUser -SqlInstance $DstInstance -Login Sql19User2 -Database $DBName | Out-Null;
Add-DbaDbRoleMember -SqlInstance $SrcInstance -User Sql17User -Database $DBName -Role db_datareader -Confirm:$False;
Add-DbaDbRoleMember -SqlInstance $DstInstance -User Sql19User -Database $DBName -Role db_datareader -Confirm:$False;
Add-DbaDbRoleMember -SqlInstance $DstInstance -User Sql19User2 -Database $DBName -Role db_datareader,db_datawriter -Confirm:$False;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment