Pages

Wednesday, January 6, 2010

List Windows Groups With Access to SQL Servers

A manager posed the question “Can we list all the Windows groups that have access to all of our database servers?”  The answer is “Yes.” 

It is very easy to do with a short PowerShell script via SMO.

The results are written to a CSV file.

   1:  ## List Windows Groups on a server ##
   2:  ## ./get-WinGrps.ps1
   3:  $start = get-date
   4:  write-host "Start: "  $start
   5:   
   6:  [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
   7:   
   8:  $FilePath = "C:\Output"
   9:  $OutFile = Join-Path -path $FilePath -childPath ("WindowsGroupsOnServers_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".csv")
  10:   
  11:  # Version inventory
  12:  @(foreach ($svr in get-content "C:\Input\TestServers.txt")
  13:  {
  14:      
  15:      $s = New-Object "Microsoft.SqlServer.Management.Smo.Server" $svr
  16:      $s.Logins | ? {$_.LoginType -eq "WindowsGroup"} | select Parent, Name, LoginType
  17:   
  18:  })  | export-csv -noType $OutFile
  19:   
  20:  $end = get-date    
  21:  write-host "End: "  $end