Friday, 17 August 2012

PowerShell Script - Find if a SQL login exists in a given list of SQL servers

I developed the below script to find list of servers where a specific login (Eg: Monitoring Account) was not yet created.
 
1:  # Script to Find SQL Server which do not have scpecific Login   
2:  # Created by - Vinoth N Manoharan  
3:  # Version 1.1  
4:  # Date - 09/05/2012  
5:  # Parameter 1 :- "-s" to run powershell for Single server  
6:  # "-f" to give filename with list of servers  
7:  # Parameter 2 :- Server name(if -s is the parameter 1) or Filename with fully #defined path(if -f is the parameter 1)  
8:  # Example1:- FindLogin.ps1 -s <servername>  
9:  # Example2:- FindLogin.ps1 -f <filename with fully define path like c:\test.txt>
  
10:  Clear-Host  
11:  $List =@()  
12:  $inpLogin = "<login_name>" #Enter Login name  
13:  $Loginlist = @()  
14:  $defaultname = @()  
15:  if($args.Length -ne 2)  
16:  {  
17:  Write-Host "Incorrect Paramenter Count use either -s or -f to specify the servername/Serverlist"  
18:  }  
19:  elseif(($args[0] -eq "-s") -or ($args[0] -eq "-S"))  
20:  {  
21:  $computer = $args[1]  
22:  if($computer -ne $null)  
23:  {  
24:  $lgn = $null  
25:  $srt = "Server Name :- " + $computer  
26:  $srt  
27:  Echo "------------------------------"  
28:  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null  
29:  $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$computer"  
30:  if (($srv.Logins |Where {($_.LoginType -eq "WindowsUser") -and ($_.Name -eq $inpLogin)}) -eq $null)  
31:  {  
32:  Write-Host "$computer - Login Not Available"  
33:  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null  
34:  $lgn = New-Object "Microsoft.SqlServer.Management.Smo.Login" ("$computer","$inpLogin")  
35:  $lgn.name = $inpLogin  
36:  $lgn.LoginType = "WindowsUser"  
37:  TRAP {Write-Host -ForegroundColor Blue "Error:Unable to Create Login on '$computer'" ;continue}  
38:  $lgn.create()  
39:  $lgn.AddToRole('sysadmin')  
40:  $lgn.Alter()  
41:  #Echo "`n"  
42:  }  
43:  Else  
44:  {  
45:  $srv.Logins |Where {($_.LoginType -eq "WindowsUser") -and ($_.Name -eq $inpLogin)}|ft Name,IsDisabled -AutoSize  
46:  }  
47:  }  
48:  }  
49:  elseif(($args[0] -eq "-f") -or ($args[0] -eq "-F"))  
50:  {  
51:  $filename = $args[1]  
52:  $computers = get-content $filename  
53:  $List = @()  
54:  $Loginlist = @()  
55:  foreach ($computer in $computers)  
56:  { #$List = $null  
57:  if($computer -ne $null)  
58:  {  
59:  $lgn = $null  
60:  $srt = "Server Name :- " + $computer  
61:  #$srt  
62:  #Echo "------------------------------"  
63:  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null  
64:  TRAP {Write-Host -ForegroundColor Blue "Error:Unable to Connect to SQL '$computer'" ;continue}  
65:  $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$computer"  
66:  if (($srv.Logins |Where {($_.LoginType -eq "WindowsUser") -and ($_.Name -eq $inpLogin)}) -eq $null)  
67:  {  
68:  #Write-Host "$computer - Login Not Available"  
69:  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null  
70:  $lgn = New-Object "Microsoft.SqlServer.Management.Smo.Login" ("$computer","$inpLogin")  
71:  $lgn.name = $inpLogin  
72:  $lgn.LoginType = "WindowsUser"  
73:  TRAP {Write-Host -ForegroundColor Blue "Error:Unable to Create Login on '$computer'" ;continue}  
74:  $lgn.create()  
75:  $lgn.AddToRole('sysadmin')  
76:  $lgn.Alter()  
77:  Write-Host "$computer - Login Created"  
78:  #Echo "`n"  
79:  }  
80:  Else  
81:  {  
82:  $Loginlist = $srv.Logins |Where {($_.LoginType -eq "WindowsUser") -and ($_.Name -eq $inpLogin)}  
83:  $List += $Loginlist  
84:  }  
85:  }  
86:  }  
87:  $List|ft Parent,Name, IsDisabled -AutoSize  
88:  }else  
89:  {  
90:  Write-Host "Incorrect Paramenter, use either -s or -f to specify the servername/Serverlist"  
91:  }  

Copyright © 2012 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

No comments:

Post a Comment