The Ji Village News

季庄新闻–Haidong Ji’s Blog

This is mainly for my own reference. Save it as ListSysadmin.pl. Run ListSysadmin.pl SqlServerList.txt on command line. It lists all logins that has sa (sysadmin) rights on Sql Server instance, including individual domain members in a domain group that has sysadmin rights.

SqlServerList.txt should be a text file, with each individual line contains a Sql Server instance.

This script is an improvement over Linchi’s original scrip from his Real World SQL Server Administration with Perl. The original script only takes one instance as input parameter. Note: with my current site style sheet on my web site, I think it ignores the angle brackets, so you need to fix the script when you do copy and paste.

use strict;
use Win32::ODBC;
# Import the function from the module SQLDBA::Utility
use SQLDBA::Utility qw( dbaRemoveDuplicates );
# Import functions from the module Win32::NetAdmin
use Win32::NetAdmin qw( GetDomainController
                        GroupGetMembers
                        LocalGroupGetMembersWithDomain
                        UsersExist );
Main: {
   my $log = shift or die "***Err: $0 expects a file name.\n";
   open(LOG, "$log") or die "***Err: couldn't open $log.\n";
   while (<log>) {
      my $line = $_;
      if ($line =~ /\[?(\w+)/ix) {
         my $serverConn = $1;
         print "Instance: $1\n";
         # get all the accounts/logins with sysadmin role from SQL Server
         my $loginRef = getSQLSysadmins($serverConn);
         # in case the server name is followed by an instance name
         my ($server) = $serverConn =~ /([^\\]+)(\\.+)?/;
         # replace BUILTIN with the server name
         foreach (@{$loginRef->{groupSet}}) {
            s/BUILTIN\\/uc($server) . "\\"/e; # evaluate the right side before replace
         }
         # remove all local groups and get their members
         $loginRef = removeLocalGroups($loginRef);
         # get global group members
         $loginRef = getGlobalGroupMembers($loginRef);
         # now print out all the NT/SQL logins that are SQL sysadmin
         print "Listing all NT/SQL logins with the sysadmin rights ...\n";
         foreach (sort @{$loginRef->{resultSet}}) { print "\t$_\n"; }
               }
         }
   close(LOG);
} # Main
#########################
sub getSQLSysadmins {
    my $serverConn = shift or
       die "***Err: getSQLSysadmins() expects a server.";
   my $connStr = "Driver={SQL Server};Server=$serverConn;"  .
                  "Trusted_Connection=Yes;Database=master";
   my $conn = new Win32::ODBC ($connStr) or
      die "***Err: Can't connect to $serverConn. " . Win32::ODBC::Error();
   # get sysadmin logins (NT and SQL)
   my @logins = ();
   my $sql = q/SELECT loginname FROM master.dbo.syslogins
                WHERE sysadmin = 1
                  AND isntgroup = 0/;
   unless ($conn->Sql( $sql )) {
       @logins = (@logins, $conn->Data) while ($conn->FetchRow());
   } else {
       print "***Err: executing $sql. ", Win32::ODBC::Error(), "\n";
   }
   # get sysadmin nt groups
   my @groups = ();
   my $sql =  q/select loginname from master.dbo.syslogins
                 where sysadmin = 1
                   and isntgroup = 1/;
   unless ($conn->Sql( $sql )) {
       @groups = (@groups, $conn->Data) while ($conn->FetchRow());
   } else {
       print "***Err: executing $sql. ", Win32::ODBC::Error(), "\n";
   }
   $conn->Close();
   return { resultSet  => \@logins,
            groupSet   => \@groups,
            userSet    => undef };
} # getSQLSysadmins
###########################
sub removeLocalGroups {
    my $loginRef = shift or
         die "***Err: removeLocalGroups() expects a reference.";
    foreach my $group (@{$loginRef->{groupSet}}) {
        if (my $rc = getLocalGroupMembers($group)) {
            push @{$loginRef->{userSet}}, @{$rc};
        }
        else {
            push @{$loginRef->{userSet}}, $group;
        }
    }
    delete $loginRef->{groupSet};
    return $loginRef;
} # removeLocalGroups
##############################
sub getGlobalGroupMembers {
    my $loginRef = shift or
          die "***Err: getGlobalGroupMembers() expects a reference.\n";
    my @logins = ();
    foreach my $user (@{$loginRef->{userSet}}) {
        if ($user =~ /^NT\s+AUTHORITY\\/) {
            push @logins, $user;
            next;
        }
        my ($domain, $name) = $user =~ /^(.+?)\\(.+)$/;
        if ( UsersExist($domain, $name ) ) { # a user account
            push @logins, $user;
        }
        else {
            my $pdc;
            my @users;
            if (GetDomainController(undef, $domain, $pdc)) {
                $pdc =~ s/\\//g;
                if ( UsersExist($pdc, $name ) ) {
                    push @logins, $user;
                }
                else {  # $name is a global group account
                    # get users in the global group
                    if (GroupGetMembers($pdc, $name, \@users)) {
                        # prefix the users with the domain name
                        push @logins, map { "$domain\\$_"; } @users;
                    }
                    else {  # you shouldn't reach here
                        print "***Err: can't retrieve users for $domain\\$name.\n";
                        next;
                    }
                }
            }
            else {
                print "***Err: can't find domain controller for $domain.\n";
                next;
            }
        }
    }
    push @logins, @{$loginRef->{resultSet}};
    $loginRef->{resultSet} = dbaRemoveDuplicates(\@logins);
    return $loginRef;
} # getGlobalGroupMembers
################################
sub getLocalGroupMembers {
    my $group = shift or
        die "***Err: getLocalGroupMember() expects a group name.";
    my ($domain, $group_name) = $group =~ /^(.+?)\\(.+)$/  or
        die "***Err: $group must be qualified with a domain/machine name.";
    my @users = ();
    if (LocalGroupGetMembersWithDomain($domain, $group_name, \@users)) {
        return \@users;
    }
    else {
        return undef;
    }
}  # getLocalGroupMembers