██████╗ ██████╗ █████╗ ████████╗ ██████╗ ██████╗ ██╗ ███████╗ ██╔══██╗██╔══██╗██╔══██╗╚══██╔══╝██╔═══██╗██╔═══██╗██║ ██╔════╝ ██║ ██║██████╔╝███████║ ██║ ██║ ██║██║ ██║██║ ███████╗ ██║ ██║██╔══██╗██╔══██║ ██║ ██║ ██║██║ ██║██║ ╚════██║ ██████╔╝██████╔╝██║ ██║ ██║ ╚██████╔╝╚██████╔╝███████╗███████║ ╚═════╝ ╚═════╝ ╚═╝ ╚═╝ ╚═╝ ╚═════╝ ╚═════╝ ╚══════╝╚══════╝ ██████╗ ███████╗ ██████╗██╗██████╗ ███████╗ ██╗ ██╗ ██████╗ ██████╗ ██╔══██╗██╔════╝██╔════╝██║██╔══██╗██╔════╝ ████████╗██╔═████╗╚════██╗ ██████╔╝█████╗ ██║ ██║██████╔╝█████╗ ╚██╔═██╔╝██║██╔██║ █████╔╝ ██╔══██╗██╔══╝ ██║ ██║██╔═══╝ ██╔══╝ ████████╗████╔╝██║██╔═══╝ ██║ ██║███████╗╚██████╗██║██║ ███████╗ ╚██╔═██╔╝╚██████╔╝███████╗ ╚═╝ ╚═╝╚══════╝ ╚═════╝╚═╝╚═╝ ╚══════╝ ╚═╝ ╚═╝ ╚═════╝ ╚══════╝ __ ___ __ __ ___ ___ __ ___ __ __ __ __ __ |__) |__ |__) | | / ` /\ | |__ |__) |__ |__) |\/| | /__` /__` | / \ |\ | /__` | \ |___ | |___ | \__, /~~\ | |___ | |___ | \ | | | .__/ .__/ | \__/ | \| .__/
"We have a new colleague, can you please grant them the exactly same permissions I have?!"
Note: IF it is an Windows Login suggest the usage of AD groups to help you and themselves.
You can find more options on my blog post: Replicate permissions to new Login/User with dbatools
In [ ]:
$dbatools1 = "localhost,1433"
$loginUser = "storageuser"
$secureString = ConvertTo-SecureString "dbatools.IO" -AsPlainText -Force
$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "sqladmin", $secureString
$newLoginUser = "storageuserColleague"
$secureString = ConvertTo-SecureString "123" -AsPlainText -Force
$newLoginCred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "storageuserColleague", $secureString
$fileLogin = "D:\Presentations\PASS Marathon Portuguese 2020\Replicate Permissions\Login.sql"
$fileUser = "D:\Presentations\PASS Marathon Portuguese 2020\Replicate Permissions\User.sql"
$fileNewLoginUser = "D:\Presentations\PASS Marathon Portuguese 2020\Replicate Permissions\NewLoginUser.sql"
$confirmPermissions = "D:\Presentations\PASS Marathon Portuguese 2020\Replicate Permissions\01_CheckPermissions.sql"
In [ ]:
# Export the login and its server-roles, server-level and database-level permissions
Export-DbaLogin -SqlInstance $dbatools1 -SqlCredential $cred -Login $loginUser -FilePath $fileLogin -Verbose
In [ ]:
# Export the user from every database and its permissions at database-roles and object level
Export-DbaUser -SqlInstance $dbatools1 -SqlCredential $cred -User $loginUser -FilePath $fileUser -Verbose
In [ ]:
((Get-Content $filelogin, $fileUser -Raw) -replace ($loginuser, $newloginuser)) -Replace '(, SID[^,]*)', ' ' | Set-content $fileNewLoginUser
In [ ]:
# Workaround #1 - Remove the 'GO's from script
$scriptWithoutGO = (Get-Content $fileNewLoginUser -Raw) -replace '\bGO\b', ' '
$scriptWithoutGO
In [ ]:
# Run the script
Invoke-DbaQuery -SqlInstance $dbatools1 -SqlCredential $cred -Query $scriptWithoutGO -Verbose
In [ ]:
# Workaround #2 - Run the changed script using the ExecuteNonQuery method
$sqlInst = Connect-DbaInstance $dbatools1 -SqlCredential $cred
$script = Get-Content $fileNewLoginUser -Raw
$sqlInst.Databases["master"].ExecuteNonQuery($script)
In [ ]:
# Reset password for a new one
Set-DbaLogin -SqlInstance $dbatools1 -SqlCredential $cred -Login $newLoginUser -SecurePassword $newLoginCred.Password