Recently the SQL Tools team announced the release of SSMS 16.3 (or SSMS 2016.3) is available for download. Along with the Management Studio update comes a number of new PowerShell features.  Among them are the new Get-SqlLogin and Remove-SqlLogin cmdlets.

Get-SqlLogin

When you run Get-SqlLogin inside of the provider it returns a full list of the Logins on that instance (example: SQLSERVER:\SQL\localhost\default> Get-SqlLogin ).  You can also run this cmdlet outside of the SQLServer provider by running something like Get-SqlLogin -ServerInstance localhost.  The Get-SqlLogin cmdlet comes with a number of nice optional parameters as well, the most straight-forward of which is probably the -LoginName parameter.  You can use the -LoginName parameter to search for a specific account, with this account being either a SQL Login or A Windows Login.  While searching for and returning a single SQL Login against one instance probably isn’t too exciting, the fact that you can include this in a chain of other commands in PowerShell is where I think you will really harness the benefit for this cmdlet.  Consider the following example where I run through every instance of my Registered Servers and look for the same SQL Login:

  foreach ($RegisteredSQLs IN Dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\ | WHERE {$_.Mode -ne 'd'} )
   {
   Get-SqlLogin -ServerInstance $RegisteredSQLs.Name -LoginName SomeOldUser
   } 
  

This gives us:

Now this does in fact give us results, however, by default it isn’t returning the name of the instance we interrogated. One easy way to solve that is pipe the results to the Select-Object cmdlet and specify the “Parent” property.  The Select-Object cmdlet comes with an alias to the word SELECT and being a SQL-Person I always use that instead.  I have also added a few additional properties that you may find helpful.

    foreach ($RegisteredSQLs IN Dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\ | WHERE {$_.Mode -ne 'd'} )
    {
    Get-SqlLogin -ServerInstance $RegisteredSQLs.Name -LoginName SomeOldUser |
    SELECT Parent, Name, LoginType, CreateDate, DateLastModified
    } 
  

Now we see more results:

With this you will be able to have more actionable results from the Get-SqlLogin cmdlet.  Unfortunately, it does appear that this cmdlet returns an error when it doesn’t find the condition you were searching for, instead of returning a message like ‘no users found’ or simply returning nothing at all.  I’ve gone ahead and filed this Connect item, which you can Up-Vote if you would prefer Get-SqlLogin to not return errors if it didn’t find a login.

Handy SwitchParameters like -Locked, -Disabled, -PasswordExpired, and -HasAccess have also been included.  For each of these parameters, the Get-SqlLogin cmdlet will only return the list of logins that meet that particular criteria.  Similarly, there is a -LoginType parameter, however, with this one you need to decide which type of login to look for. The options are: AsymmetricKey, Certificate, ExternalGroup, ExternalUser, SqlLogin, WindowsGroup, WindowsUser.  Thankfully with PowerShell you’ll never have to remember that list.  PowerShell has a feature called Parameter Validation Sets which means that you are given a list to pick from (Note: In the console you will have to rely on tab completion in order to use this feature).

Remove-SqlLogin

Remove-SqlLogin is another cmdlet that came with SSMS 16.3 and it can be very, very handy.  Let’s start with the basics, if you provide this cmdlet the name of a valid login for its -LoginName parameter and point it at a SQL instance it won’t remove the login, until you say ‘Yes’ or ‘Yes to All’.

This may come across as a nice safety feature or annoying, depending on your point of view.  Either way, if you supply the -Force parameter the cmdlet will go ahead and remove the login without any further prompting.  Removing Logins isn’t the only thing this cmdlet can do, if you supply the -RemoveAssociatedUsers parameter it will remove all database users associated to that login in every database throughout that instance, saving you from the pain of having orphaned users.  

An example of how to use the Remove-SqlLogin cmdlet might be if you wanted to remove a login from every SQL Server instance in your organization.  To do that we can use the same loop construct that we used in the previous example.  

    foreach ($RegisteredSQLs IN Dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\ | WHERE {$_.Mode -ne 'd'} )
    {
    Remove-SqlLogin -ServerInstance $RegisteredSQLs.Name -LoginName BadUser -RemoveAssociatedUsers -Force
    }
  

That should give you enough to get started using these two new cmdlets.  I encourage you to try them and out and if you’d like to dig a little deep run Get-Help -Full Get-SqlLogin to see all of the parameters available to you.