Create a Powershell script to disable most of SQL server logins-CodePudding

First, and newbie question. Sorry in advance ;)

I'm trying to create a two step script in powershell to change all sql server logins on different servers.

My goal is to be able to run it in a few seconds and not having to access the management studio for this operations. This has to be done in about 1000 computers running different versions of SQL Express.

1st step: Change the SA password.
- I've tested this one and it's ok. I'm using a txt file with the server name list and two string for the password and username. Works perfect.
2nd step: Disable all sql logins (including windows auth ones)that are not on text file with the list of logins to keep.

Here's where I'm stuck.

I can use a list of users and disable them but I would like to do the opposite. I want to disable login names that are not on the list but are enabled on the sql server.

I need it to work this way because I have no chance to know the hostnames and usernames without accessing our clients computers / servers and I need it to be done really quick without manual procedures.

This is what I've done to be able to disable the ones on the list. It works fine but... I need the opposite.

Thank you very much and sorry if it's a stupid question.

Regards, Luís

#DISABLE LOGINS ON THE LIST

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$Error.Clear()
cls
 
$servers = Get-Content C:\example\servers.txt
$logins = Get-Content C:\example\users.txt 
foreach($server in $servers)
{
    $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
     
         
    
    foreach($login in $logins)
    {
        if ($srv.Logins.Contains($login)) 
        { 
            $srv.Logins[$login].disable(); 
        }
    }
}

CodePudding user response：

$srv.Logins |
  Where-Object Name -notin $logins |
    ForEach-Object Disable

Note that the lookup isn't efficient, because a linear search in array $logins is performed for each login, but that probably won't matter in practice.

The Where-Object and ForEach-Object calls use simplified syntax.