What to do when you are genuinely bored and you need to copy MS SQL logins, between instances with dbatools.

Hi all, this is what happened today at work to me, an Accidental SQL DB Administrator, amongst other “virtues”.

The Reason?

Support team requested a new instance of SQL server 2017. That’s it!!! I will not add manually every user existed in the previously installed SQL instance to the new, period.

It’s not the process that is boring… I’m bored doing menial jobs. So, what should I do…?

Well thank God and Microsoft PowerShell exists, now how can I use this to complete the job at hand.

Google you say, right… so found this blog https://blog.netnerds.net/2016/06/its-2016-why-is-sp_help_revlogin-a-thing/.

This a well written post by Chrissy LeMaire arguing the use of the (let’s face it ) sp_help_revlogin to PowerShell. By the way sp_help_revlogin enables SQL Admins to copy users between SQL instances and/or servers, not always in straight forward manner.

So you might wonder why don’t you use sp_help_revlogin  told you I’m bored and a like PowerShell a lot.

First things first install DBatools :

1. using PowerShell with Install-Module dbatools
2. using Chocolatey with Choco install dbatools
3. clone the project from Github

There 400 commands in dbatools and you can regain sysadmin access, take back-ups effortlessly, keep track of your databases, export your users in list etc.

Well long story sort, I launched a PowerShell window and enter the following command:

Copy-dbaLogin -Source SQL1 -destination SQL1\Instance1

The above command copies all logins from default instance to another. You could also copy from instance to instance it works!!!!!