InvalidLogins.pl

#Program: Reports SQL Server AD accounts which are now longer valid by verifying the
# output of sp_validlogins system stored procedure against SID stored in AD. This
# is done to ensure only accounts that do not exist are reported since sp_validlogins
# will only check name and not SID against AD, accounts that are renamed will retain
# the same SID.
# Requires Perl Package Win32::SqlServer
# Download from http://www.sommarskog.se/mssqlperl/Win32-SqlServer-2.002.zip
# unzip and run activeperl-copy.pl to install
#Run ppm   s Win32::Security to install Security module
#Author: Chad Miller cmille19@tampabay.rr.com
#Created: August 7 2006

use strict;
use Getopt::Std;
use Win32::Security::SID;
use Win32::SqlServer;

Main: {
  my $serversAR = getOpts();
  getInvalidLogins($serversAR);
 } #Main

#######################
sub getOpts {
  my %opts;
  my @servers;
  my $report;

  getopts('S:C:', \%opts);

   #Help or missing Server or Config file
   if ($opts{'h'} or !defined $opts{S} and !defined $opts{C}) {
    printUsage();
   }

  #Servers specified with -S parameter
   if (exists $opts{S}) {
    if (defined $opts{S}) {
      @servers = split(/\s*,\s*/, $opts{S});
    }
   }

   #Config file specified with -C parameter
   if (exists $opts{C}) {
    if (defined $opts{C}) {
      open (FH, $opts{C}) || die "Couldn't open file: $!";
      while ( <FH> )
      {
        chomp;
        push @servers, $_;
      }
      close(VALIDFILE);
    }
   }

   return (\@servers);
} #getOpts

#######################
sub getInvalidLogins {
  my $serversAR = shift;

  foreach my $server (@$serversAR) {
    my $sqlsrv = Win32::SqlServer::sql_init($server, undef, undef, 'master');
    my @result = $sqlsrv->sql_sp('sp_validatelogins', Win32::SqlServer::LIST);

    foreach my $row (@result) {
      my $binsid = pack "H*", $$row[0]; #Convert SID to binary SID format
      #if unable to resolve SID print output
      if (grep /^S-1-5/, Win32::Security::SID::ConvertSidToName($binsid)) {
        print "$server,$$row[1]\n";
      }
    }
  }

} #getInvalidLogins

#######################
sub printUsage {
  print << '--Usage--';
Usage:
perl invalidlogins.pl {server(s)|Config file}
   [-h Print Usage info]
   [-S Server(s) CSV of servers]
   [-C Config file listing servers]
--Usage--
  exit;
} #printUsage