PowerShell/dbatools Tip – Clone a login / database user

One of the more frequent requests that a SQL Server DBA receives is to “Clone a login” with all its permissions. For example a request could be

Clone BILL_BLACK’s login and create a new login JACK_JOHNSON with exactly the same privileges in all databases.
Clone AD group login BILLING_APP_ADMINS and create a login for new AD group BILLING_APP_CONSULTANTS with the same permissions as BILLING_APP_ADMINS in all databases

Before dbatools, each DBA would have his/her own script to accomplish this. With dbatools function Copy-DbaLogin, it is super simple but a lot of DBA’s still do not know this so I decided to share this simple tip on the usage.

Copy-DbaLogin example

Copy-DbaLogin `
    -LoginRenameHashtable @{"Domain\BILLING_APP_ADMINS" = "Domain\BILLING_APP_CONSULTANTS"} `
    -Source MySourceInstance `
    -Destination MyTargetInstance `
    -Login 'Domain\BILLING_APP_ADMINS' `
    -ObjectLevel `
    -NewSid `
    -Verbose -Force

In the example above we make an exact copy of an AD group and all the permissions it has in the different databases. If the Source and Destination are the same, it creates a copy on the same instance. The key thing to note is the LoginRenameHastable parameter which provides the mapping from the old name to the new login name.

Clone a Database User in a single DB:

Sometimes, you may have to clone a database user for which login already exists but you want to clone that to create another database user in just one database on the instance (remember Copy-DbaLogin can clone login and DB user across all databases). This script is how you can do so:

#Copy a database user from source to target 
# (assuming login already exists!)
$sourceDb = 'MySourceDB'
$targetDb = 'MyTargetDB'
$sourceServer = 'MySourceInstance'
$targetServer = 'MyTargetInstance'
$sourceUser = 'domain\source_user'
$targetUser = 'domain\cloned_user'


#Check if the user exists in source
Get-DbaDbUser -SqlInstance $sourceServer -Database $sourceDb | 
        Where-Object {$_.Name -eq $sourceUser} |
        Select-Object name, login, createdate | ft -AutoSize

#Get the script for the DB user
$userScriptSource = Export-DbaUser `
                        -SqlInstance $sourceServer `
                        -Database $sourceDb `
                        -User $sourceUser `
                        -Passthru #-FilePath $outFile 

#Replace source user with target user name
$userScriptTarget = [Regex]::Replace(`
                         $userScriptSource, `
                         [regex]::Escape($sourceUser), `
                         $targetUser, `
                         [System.Text.RegularExpressions.RegexOptions]::IgnoreCase)


#Remove the user if it exists already in target
Get-DbaDbUser -SqlInstance $targetServer -Database $targetDb | 
        Where-Object {$_.Name -eq $targetUser} |
        Remove-DbaDbUser

#Create the user in target
Invoke-Sqlcmd `
        -ServerInstance $targetServer `
        -Database $targetDb `
        -Query $userScriptTarget

Taking control of the scripting process

A lot of times, you may need to do more than copy a login from one instance to another. You may have to rename the login or clone a login across servers or clone a login only for certain databases etc.

If you want more control over the scripting process, then you can do exactly that using another dbatools command Export-DbaLogin. You can generate the script, alter it, save it etc., but it will have GO statements. The one deficiency of Invoke-DbaQuery (at least as of Oct 5, 2020) is that it cannot run a script with GO statements. We could replace GO’s but it is just easier to use Invoke-SqlCmd from the SQLPS or SQLServer module which supports it. Below I will show you a couple of examples of what it is like to do custom stuff using the dbatools commands.

Clone a login across servers for specific DB’s

In this example below, we have a list of logins that we need to clone in a bunch of servers for a specific list of databases. I have left some commented code in place for you to experiment with the other options. Here is the requirement

We have a list of databases
We have a list of Servers
Within all the servers, for the databases, clone a login and corresponding database user

This is like a scenario when a new employee comes in and has to have the same permissions as an existing employee in certain/all databases. This is just for an example because I recommend using AD groups and roles instead.

$dbList = @('BillingDB', 'InventoryDB',  'HR', 'SalesDB',  'Marketing')
$serverList = @('MyServer1', 'MyServer2\Instance1', 'MyServer3', 'MyServer4')
$sourceLogin = 'Domain\BILLING_APP_ADMINS'
$targetLogin = 'Domain\BILLING_APP_CONSULTANTS'
$fileExportPath = 'C:\temp\20201001_Copy_Login'

foreach($server in $serverList)
{
    foreach($db in $dbList)
    {
        
        $loginScriptSource = Export-DbaLogin `
            -SqlInstance $server `
            -Login $sourceLogin `
            -Database $db `
            -Passthru 
            #-FilePath $file

        #$loginScriptSource = (Get-Content $file | Out-String)

        $loginScriptTarget = [Regex]::Replace(`
             $loginScriptSource, `
             [regex]::Escape($sourceLogin), `
             $targetLogin, `
             [System.Text.RegularExpressions.RegexOptions]::IgnoreCase)
                
        #Export to file optionally
        #$file = Join-Path  $fileExportPath -ChildPath ("$server" + "_" + $db + ".sql")        
        #$file
        #$loginScriptTarget | Out-File $file -Force ascii

        #Run the script using Invoke-Sqlcmd (Invoke-DbaQuery does not support GO statements)
        #Invoke-DbaQuery `
        #    -SqlInstance $server `
        #    -Database $db `
        #    -Query $loginScriptTarget `
        #    -CommandType Text


        #Invoke-Sqlcmd could be from SQLPS or SQLServer depending on SQL Server PowerShell module you are using
        #(get-command Invoke-Sqlcmd).Module.Path
        #C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

        Invoke-Sqlcmd `
            -ServerInstance $server `
            -Database $db `
            -Query $loginScriptTarget
    }
}

If you wanted to check the script it generates for the logins to make sure that is what you want, uncomment the portion of the code that writes the script to a file.

I have included a sample script that was generated for a database is below so that you get a general idea of what it generates.

/*
	Created by DOMAIN\USERID using dbatools Export-DbaLogin for objects on BILLINGSERVER at 2020-10-01 15:51:02.890
	See https://dbatools.io/Export-DbaLogin for more information
*/
USE master

GO
IF NOT EXISTS (SELECT loginname FROM master.dbo.syslogins WHERE name = 'DOMAIN\BILLING_APP_ADMINS') CREATE LOGIN [DOMAIN\BILLING_APP_ADMINS] FROM WINDOWS WITH DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english]
GO
ALTER SERVER ROLE [processadmin] ADD MEMBER [DOMAIN\BILLING_APP_ADMINS]
GO

USE master

GO
Grant ALTER ANY CONNECTION TO [DOMAIN\BILLING_APP_ADMINS]  AS [sa]
GO
Grant CONNECT SQL TO [DOMAIN\BILLING_APP_ADMINS]  AS [sa]
GO
Grant VIEW SERVER STATE TO [DOMAIN\BILLING_APP_ADMINS]  AS [sa]
GO

USE [BILLING_DB]

GO
CREATE USER [DOMAIN\BILLING_APP_ADMINS] FOR LOGIN [DOMAIN\BILLING_APP_ADMINS] WITH DEFAULT_SCHEMA=[DOMAIN\BILLING_APP_ADMINS]
GO
ALTER ROLE [BILLING_INBOX_EDITOR] ADD MEMBER [DOMAIN\BILLING_APP_ADMINS]
GO
ALTER ROLE [BILLING_INBOX_VIEWER] ADD MEMBER [DOMAIN\BILLING_APP_ADMINS]
GO
ALTER ROLE [BILLING_PROCESS_DESIGNER] ADD MEMBER [DOMAIN\BILLING_APP_ADMINS]
GO
ALTER ROLE [BILLING_PROCESS_RUNNER] ADD MEMBER [DOMAIN\BILLING_APP_ADMINS]
GO
ALTER ROLE [BILLING_PROCESS_VIEWER] ADD MEMBER [DOMAIN\BILLING_APP_ADMINS]
GO
ALTER ROLE [BILLING_SUPERVISOR] ADD MEMBER [DOMAIN\BILLING_APP_ADMINS]
GO
ALTER ROLE [BILLING_UPGRADER] ADD MEMBER [DOMAIN\BILLING_APP_ADMINS]
GO
ALTER ROLE [db_datareader] ADD MEMBER [DOMAIN\BILLING_APP_ADMINS]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [DOMAIN\BILLING_APP_ADMINS]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [DOMAIN\BILLING_APP_ADMINS]
GO
Grant CONNECT TO [DOMAIN\BILLING_APP_ADMINS]  AS [dbo]
GO
Grant SHOWPLAN TO [DOMAIN\BILLING_APP_ADMINS]  AS [dbo]
GO

If you were just playing with the code and want to cleanup the temporary users you created, simply drop the login and clean-out the orphaned user from the database(s)

#If you were just playing around, you can cleanup the cloned login
Remove-DbaLogin `
    -SqlInstance 'MyServer2\Instance1' `
    -Login 'Domain\BILLING_APP_ADMINS' `
    -Confirm: $false

Remove-DbaDbOrphanUser `
    -SqlInstance 'MyServer2\Instance1' `
    -User 'Domain\BILLING_APP_ADMINS' `
    -Confirm: $false    
#To remove the orphans in a specific database only, just include the "Database" parameter. 
#-Database 'MyDatabase'

Clone all users only in a specific DB across servers with user renaming and database renaming

In this example below, we have a list of users in DatabaseA of ServerA for which corresponding logins exit. There a lot of additional logins that ServerA has than what the DatabaseA does. We need to copy all the users and their logins in Server1.DatabaseA to Server2.DatabaseB and

Create the login in ServerB if it does not exist
Create the user in ServerB.DatabaseB if it does not exist
Copy the login permissions as it exists on DatabaseA
Copy the database permissions as it exists on ServerA.DatabaseA
Perhaps rename some of the users/logins along the way
Users that are not in DatabaseA should not be involved at all. ServerA may have 100’s of other logins with no corresponding users in DatabaseA.

Below is the script to accomplish the above. The scripts this generates is similar to the TSQL script in the previous example. Please feel free to alter as necessary:


#----------------------------------------------------------------------------
#Copy all the logins in a specific database on a source server 
#  to a target server and database
#  and create the appropriate logins if they dont exist
#Do remapping (renaming of logins) if necessary
#----------------------------------------------------------------------------
$sourceServer = 'MySourceServer\InstanceS'
$targetServer = 'MyTargetServer\InstanceT'
$sourceDB = '[SourceDB]'  #Include square-brackets to eliminate bad partial string replacements
$targetDB = '[TargetDB]'  #Include square-brackets to eliminate bad partial string replacements

#If replacements of source login/user is necessary provide a mapping..else an empty hashtable
$sourceToTargetUserMapping = @{
                                #"SourceUserOrLoginName1" = "TargetUserOrLoginName1";
                                #"SourceUserOrLoginName2" = "TargetUserOrLoginName2"
                                }

#Get the list of source DB users and copy them over to target
$sourceDBUsers = Get-DbaDbUser `
                    -SqlInstance $sourceServer `
                    -Database $sourceDB

 
foreach($sourceDBUser in $sourceDBUsers)
{
    if ($sourceDBUser.Login.Trim().Length -gt 0)
    {
        
        [string] $loginScriptSource = Export-DbaLogin `
            -SqlInstance $sourceServer `
            -Login $sourceDBUser.Login `
            -Database $sourceDB `
            -Passthru
            #-FilePath $file
 
        
        if ($loginScriptSource.Trim().Length -gt 0)
        {
            $loginScriptTarget = $loginScriptSource

            if ($sourceToTargetUserMapping.Count -gt 0)
            {
                if ($sourceToTargetUserMapping.ContainsKey($sourceDBUser.Login))
                {
                    #Replace the source login with target login name
                    $loginScriptTarget = [Regex]::Replace(`
                         $loginScriptSource, `
                         [regex]::Escape($sourceDBUser.Login), `
                         $sourceToTargetUserMapping[$sourceDBUser.Login], `
                         [System.Text.RegularExpressions.RegexOptions]::IgnoreCase)


                    #If source and target database names are different, may have to
                    # replace database names without replacing other strings that have the db name partially

                    if ($sourceDB.StartsWith('[') -and $sourceDB.EndsWith('[') `
                            -and $targetDB.StartsWith('[') -and $targetDB.EndsWith('['))
                    {
                        #Replace the source database name with target database name
                        $loginScriptTarget = [Regex]::Replace(`
                             $loginScriptSource, `
                             [regex]::Escape($sourceDB), `
                             $targetDB, `
                             [System.Text.RegularExpressions.RegexOptions]::IgnoreCase)
                    }
                }
            }


            "Working on $sourceDBUser"
            #------------------------------
            $loginScriptTarget

            #Invoke-Sqlcmd could be from SQLPS or SQLServer depending on SQL Server PowerShell module you are using
            #(get-command Invoke-Sqlcmd).Module.Path
            #C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

            #Create the login and user plus do the grants
            Invoke-Sqlcmd `
                -ServerInstance $targetServer `
                -Database $targetDB `
                -Query $loginScriptTarget 
        }
    }
}

Hopefully, you found this post helpful. Please comment and share your thoughts/code.

One thought on “PowerShell/dbatools Tip – Clone a login / database user”

Leave a comment Cancel reply