- Assign the id to be searched to the $searchid variable
- Open the Powershell ISE Editor or Powershell command line.
- Execute it . It will tell you if your login is there in the SQL Server or not.
- Assign the id to be searched to the $searchid variable
- Open the Powershell ISE Editor or Powershell command line.
- Execute it . It will tell you if your login is there in the SQL Server or not.
#This script will help you to find out whether a particular id is present in the SQL Server
#Using foreach you can use this to find the ids in multiple servers as well.
Clear-content 'D:\logfile.txt'
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$logfile='D:\logfile.txt'
$searchid='Give the ID here to search'
foreach($server in $servers)
{
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
#This sets the connection to mixed-mode authentication
$srv.ConnectionContext.LoginSecure=$true;
$dbs=$srv.Logins
if($dbs.contains('$searchid'))
{
$server+','+'ID found' | Out-File $logfile -Append
}
else
{
$server+','+'Nil'+',' | Out-File $logfile -Append
}
}