As many of you know the system stored procedure sp_validatelogins is used for finding invalid logins. Although sp_validatelogins is useful there's one problem -- the output isn't always accurate. You see when you add a a Windows account to SQL Server the SID as well as the domain (or computer name) slash account name are stored in master database, if the account is renamed in Active Directory or in the case of local users on the local system, the account stills retains access to SQL Server. How is this possible? That's because the SID is unchanged and that is what SQL Server uses. When you run sp_validatelogins the account name is validated but not the SID and a valid but rename account is returned.

 

So, what we need to do is make sp_validateLogins accurate by resolving the SID against Active Directory or the local system. As add bonus we should return the rename account name. Fortunately this is pretty easy with a little Powershell script. The following is a standalone excerpt from SQL Server PowerShell Extensions, edited to work with Microsoft's sqlps:

 

function Get-InvalidLogins           
{           
    param($ServerInstance)           
           
    foreach ($r in Invoke-SqlCmd -ServerInstance $ServerInstance -Database 'master' -Query 'sp_validatelogins')           
    {           
        $NTLogin = $r.'NT Login'           
        $SID = new-object security.principal.securityidentifier($r.SID,0)           
        $newAccount = $null           
        trap { $null; continue } $newAccount = $SID.translate([system.security.principal.NTAccount])           
       if ($newAccount -eq $null) {            
        $isOrphaned = $true           
        $isRenamed = $false           
        }           
       else {           
        $isOrphaned = $false           
        $isRenamed = $true           
        }           
        if ($NTLogin -ne $newAccount) {           
        new-object psobject |           
        add-member -pass NoteProperty NTLogin $NTLogin |           
        add-Member -pass NoteProperty TSID $SID |           
        add-Member -pass NoteProperty Server $ServerInstance |           
        add-Member -pass NoteProperty IsOrphaned $isOrphaned |           
        add-Member -pass NoteProperty IsRenamed $isRenamed |           
        add-Member -pass NoteProperty NewNTAccount $newAccount           
        }           
    }           
           
} #Get-InvalidLogins

 

To use the script simply copy and paste the function defintion into a sqlps session or alternatively you can add the function to your Windows Powershell profile.

 

Next simply call the function specifying a SQL Server instance:

 

The original idea for the code came from a blog post which uses a CLR solution.  In my pre-Powershell days (2006) I created this Perl script.