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 () {
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