12 Days of dbatools – Fifth Day2 min read

---

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

---

Command Information:

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.

Command Description: 

Copy-DbaLogin

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.

Export-DbaLogin

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.

Get-DbaLogin

The Get-DbaLogin function returns an SMO Login object for the logins passed, if there are no users passed it will return all logins.

New-DbaLogin

Creates a new SQL Server login with provided specifications

Remove-DbaLogin

Tries a bunch of different ways to remove a Login or two or more.

Rename-DbaLogin

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

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.

Command Example:

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

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.