This blog post is about how brilliant dbatools are. In this case – for syncing user logins between SQL Server instances.
Whenever I do my “DevOps and the DBA” talk I dedicate a minute or two talking about dbatools.
You can find the tools here:
Simply download and install on your server – or a machine that has visibility to what you want to connect to.
When building new servers the most important thing after restoring and securing the database is syncing up the users. This is especially important for Availability Groups as SQL Authenticated users required the SIDS to be the same.
In the past I had some very long winded code that would do the sync – it was a mixture of TSQL and PowerShell. It worked but you know – it was cumbersome.
So I effectively practiced what I preached recently and used the Copy-DbaLogin command – actually I looked at what it did first by running:
Get-Help Copy-DbaLogin -Detailed
For what I needed to do – I needed both the SQL Authenticated users AND all Windows users/groups – so I just ran this:
Copy-DbaLogin -Source OLDSERVER -Destination NEWServer
Which results in the following example:
The upshot of all this is that syncing users between SQL Server instances has never been easier and means I can throw away my terribly written script.