Technical Article

Powershell Script to find a SQL\Windows login in SQL Server

,

- 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 
}

}

Rate

2.4 (5)

Share

Share

Rate

2.4 (5)