On the first day of Christmas dbatools gave to me: Find-DbaCommand
On the second day of Christmas dbatools gave to me: Get-DbaCmsRegServer
On the third day of Christmas dbatools gave to me: DbaMaxMemory
On the fourth day of Christmas dbatools gave to me: DbaAgentJob
On the fifth day of Christmas dbatools gave to me: DbaLogin
On the fifth day of dbatools, we will focus on another great command for managing your environment. That is the DbaLogin command that comes with a Copy, Export, Get, New, Remove, Rename and Set command. Incredible seven commands to help you do anything with a SQL Login that you require. Need Disaster Recovery for your Logins, Export-DbaLogin will be your command. Starting to use AlwaysOn Availability Groups, check out the Copy-DbaLogin command. What if your security team comes to you and says who has access to SQL Server, go with the Get-DbaLogin command to generate a report.
SQL Server 2000: Migrates logins with SIDs, passwords, server roles and database roles.
SQL Server 2005 & newer: Migrates logins with SIDs, passwords, defaultdb, server roles & securables, database permissions & securables, login attributes (enforce password policy, expiration, etc.)
The login hash algorithm changed in SQL Server 2012, and is not backwards compatible with previous SQL Server versions. This means that while SQL Server 2000 logins can be migrated to SQL Server 2012, logins created in SQL Server 2012 can only be migrated to SQL Server 2012 and above.
Exports Windows and SQL Logins to a T-SQL file. Export includes login, SID, password, default database, default language, server permissions, server roles, db permissions, db roles.
The Get-DbaLogin function returns an SMO Login object for the logins passed, if there are no users passed it will return all logins.
Creates a new SQL Server login with provided specifications
Tries a bunch of different ways to remove a Login or two or more.
There are times where you might want to rename a login that was copied down, or if the name is not descriptive for what it does.
It can be a pain to update all of the mappings for a specific user, this does it for you.
Set-DbaLogin will enable you to change the password, unlock, rename, disable or enable, deny or grant login privileges to the login. It’s also possible to add or remove server roles from the login.
Get-DbaLogin -SqlInstance localhost\sql2016 | Out-GridView
Lets get a list of logins from our localhost\sql2016 instance.
So we have our list of logins, now lets copy them over to our new SQL 2017 instance
Copy-DbaLogin -Source localhost\sql2016 -Destination localhost\sql2017
Advanced Command Usage:
Now lets take a look at how you can protect yourself from a server failure or an over eager Junior DBA. Will will do an Export-DbaLogin and save our logins in a nice safe file that can be executed in the event of a catastrophe.
Export-DbaLogin -SqlInstance localhost\sql2016 -Path C:\Temp\sql2016_Logins.sql