dbatools.io

SQL Server accounts

 There are two types of ‘user’ accounts, an Active Directory (AD) and an internal account. Each account has a unique ID but the issue is if you need to create another internal user account although the username will be the same the ID will be different.

Fixing internal accounts to work with migrated databases

If a database is migrated from a different server and a new internal account needs to be created to match the old user credentials the ID will be different I use the following command to fix the user account:

sp_change_users_login ‘AUTO_FIX’, ‘<broken username>’;

If all your accounts are on AD this won’t be an issue as the ID will be consistent.

dbatools.io

 I remember seeing a presentation on an online seminar from Jess Pomfret using ‘dbatools‘ to migrate database and user accounts. I found a presentation on youtube called ‘LifeHacks – easy database migrations with dbatools‘.

The downside is that you need to install the modules but they are free and feature-rich. If you do not use dbatools check out their website and the commands.

The command I used Copy-DbaLogin, the account I used to run the command was an admin on both servers to I simply ran the command defining the source, destination and the account name and dbatools did the rest:

## Migrate login

$migrateLoginSplat = @{

    Source      = “<source server URL>“

    Destination = ‘<destination server URL>‘

    Login       = “<account_name>”

    Verbose     = $true

}

Copy-DbaLogin @migrateLoginSplat

The command copied the account over, set the id to the correct value and copied over the password so within seconds all of the required accounts had been transferred with little effort.

I would like to thank Jess Pomfret and I highly suggest you look at dbatools.io.