#PSTip Validate if a SQL login exists using PowerShell

Note: This tip requires PowerShell 3.0 or above.

Before performing tasks like adding roles to a SQL login, it is desired to validate the existence of SQL login. This can be done using the Server SMO.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$smo = New-Object Microsoft.SqlServer.Management.Smo.Server $env:ComputerName
if (($smo.logins).Name -contains 'MyServerLogin') {
    "SQL login exists"
} else {
    "SQL login does not exist"
}

We can wrap this into a reusable function.

Function Test-SQLLogin {
    param (
        [string]$SqlLogin
    )
    Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
    $smo = New-Object Microsoft.SqlServer.Management.Smo.Server $env:ComputerName
    if (($smo.logins).Name -contains $SqlLogin) {
       $true
    } else {
       $false
    }
}
Test-SQLLogin -SqlLogin 'domain\sqluser'
Test-SQLLogin -SqlLogin 'sqlsauser'
Share on: