2

#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'
Filed in: Columns, Tips and Tricks Tags: , , ,

2 Pingbacks/Trackbacks

2 Responses to "#PSTip Validate if a SQL login exists using PowerShell"

Leave a Reply

Submit Comment

© 2014 PowerShell Magazine. All rights reserved. XHTML / CSS Valid.
Proudly designed by Theme Junkie.
%d bloggers like this: