Friday, 17 August 2012

PowerShell Script - Disable SQL login in a list of SQL Server


I created the below script the below script to disable a login in all SQL environment of an application when a user  access needs to be revoked temporarily.
 
1:  # Script to Disable Login on given list of SQL Server  
2:  # Created by - Vinoth N Manoharan  
3:  # Version 1.1  
4:  # Date - 09/05/2012

5:  Clear-Host  
6:  $strComputers=@("Server1\Inst1","Server2\Inst2","Server3\Inst3",  
7:  " Server4\Inst4 "," Server5\Inst5 ") # Provide the list of server where login need #to be disabled  
8:  $inpLogin = "<loginname>" # Provide the login name to be disabled  
9:  $Loginlist = @()  
10:  foreach($computer in $strComputers)  
11:  {  
12:  $srt = "Server Name :- " + $computer  
13:  $srt  
14:  Echo "------------------------------"  
15:  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null  
16:  $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$computer"  
17:  $Loginlist = $srv.Logins |Where {($_.LoginType -eq "sqlLogin") -and ($_.Name -eq $inpLogin) -and(!$_.IsDisabled)}  
18:  if($Loginlist -ne $null)  
19:  {  
20:  foreach($Login in $Loginlist)  
21:  {  
22:  $Login.Disable()  
23:  }  
24:  $srv.Logins |Where {($_.LoginType -eq "sqlLogin") -and ($_.Name -eq $inpLogin)}|ft Name, IsDisabled-AutoSize  
25:  }else  
26:  {  
27:  Write-Host "Login Not Available or It Is Disabled Already"  
28:  Echo "`n"  
29:  }  
30:  #$srv.Logins |Where {($_.LoginType -eq "sqlLogin") -and ($_.Name -like 'ssn*')}|ft Name, IsDisabled -AutoSize  
31:  Echo "`n"  
32:  }  

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

2 comments:

  1. Wonderful job.thanks for your job. hermes kelly

    ReplyDelete
  2. Is there a way to Enable it via powershell as well?

    ReplyDelete