Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Identify Local Administrators on a SQL Server box using PowerShell


By:   |   Read Comments (2)   |   Related Tips: 1 | 2 | 3 | More > Security

Attend a SQL Server Conference for FREE >> click to learn more


Problem

I want to determine who the local administrators are on each of my SQL Servers. Basically, I'd like a nightly job to run against each of my SQL Servers and produce a list of admins for each server for my review. How can I do this?

Solution

If you're looking for an interactive solution using PowerShell to determine membership of a local group on a server, then PowerShell.com has an excellent script that does everything you need. It is written by Aleksandar NikoliŠ, a PowerShell MVP. One of the things I like about it is it retrieves the adsPath property for the group, which allows us to determine whether the members of the group are for the local computer or the domain. Unfortunately, the information adsPath returns for us is a text string which contains the domain/computer name lumped together with the user or group name. That's why this script makes use of the split method to separate that information and report it back properly.

The limitation of this script is it requires interactive input and it only hits one server at a time. If you're wanting to run a batch job against multiple SQL Servers, we can use it as a starting point, but there are some changes we have to make to get things to work.

First, we have to remove the lines that take interactive input. The lines that use the cmdlet Read-Host are the ones that tell PowerShell to prompt the user for information. Those have to go. Second, we need to set the group, which will be simply "Administrators" because we're looking for local Administrators on each of the SQL Server. we accomplish this by:

$localgroupName = "administrators"

Third, we also want to retrieve a list of Windows servers to check and a simple text file with a server name on each line will do. For instance, our text file might look like this:

HR-SQL.mydomain.com
Sales-SQL.mydomain.com
Test-SQL.mydomain.com

To read this file and process each entry in turn, we'll wrap the logic that retrieves the group list using a foreach loop and use the cmdlet Get-Content to retrieve the server names. So effectively, we need to do this:

foreach ($computerName in Get-Content "$filesPath\SQLServers.txt")
{
  # Code to retrieve group information
}

The $filesPath is a variable which contains the path to the text file holding the names of our servers. Based on this sample code, the text file would be named SQLServers.txt. Like with $localgroupName, this would need to be set before the foreach loop. You'll be able to see it clearly in our final solution.

Fourth, and finally, we'll want to write out findings somewhere, because writing to the console doesn't do us much good for a batch job that runs wtihout interaction. We can use the Export-CSV cmdlet to store the results in .CSV files (readable by Microsoft Excel, for instance) and in order to ensure we write only the information we absolutely need, we will also use the Select-Object cmdlet to filter what we have. Putting these two commands together and using the pipeline function, we get this:

$members | Select-Object Name,Domain,Class | export-csv "$filesPath\$computername.csv"

In this case, the PowerShell script will write a separate .CSV file for each server using the server name as the basis for the file name.

If we put all this together, incorporating the changes, we'll have a final script that does the job for us:

# Adapted from: http://powershell.com/cs/media/p/3215.aspx
 
# List local group members on the local or a remote computer  
  
$localgroupName = "administrators"
$filesPath = "C:\scripts\audit"
 
foreach ($computerName in Get-Content "$filesPath\SQLServers.txt")
{
    if([ADSI]::Exists("WinNT://$computerName/$localGroupName,group")) {  
  
        $group = [ADSI]("WinNT://$computerName/$localGroupName,group")  
  
        $members = @()  
        $Group.Members() |  
        % {  
            $AdsPath = $_.GetType().InvokeMember("Adspath", 'GetProperty', $null, $_, $null)  
            # Domain members will have an ADSPath like WinNT://DomainName/UserName.  
            # Local accounts will have a value like 
            # WinNT://DomainName/ComputerName/UserName.  
            $a = $AdsPath.split('/',[StringSplitOptions]::RemoveEmptyEntries)  
            $name = $a[-1]  
            $domain = $a[-2]  
            $class = $_.GetType().InvokeMember("Class", 'GetProperty', $null, $_, $null)  
  
            $member = New-Object PSObject  
            $member | Add-Member -MemberType NoteProperty -Name "Name" -Value $name  
            $member | Add-Member -MemberType NoteProperty -Name "Domain" -Value $domain  
            $member | Add-Member -MemberType NoteProperty -Name "Class" -Value $class  
  
            $members += $member  
        }  
        
        # The back tick (`) tells PowerShell the command continues on the next line
        $members | Select-Object Name,Domain,Class `
          | export-csv "$filesPath\$computername.csv"  
          
    }  
    else {  
        Write-Warning `
           "Local group '$localGroupName' doesn't exist on computer '$computerName'"  
    } 
}

Of course, ensure you run the script under the context of a user account that has the ability to check the membership of the administrators group on all the servers you have listed in the text file.

Next Steps


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, November 11, 2015 - 10:32:21 AM - Ademir Back To Top

 

Is there how to list if these Logins are Enable or Disable with this script?


Wednesday, June 15, 2011 - 8:31:31 AM - Raymond Barley Back To Top

This is a great tip.

 

SQL Server Agent in SQL Server 2008 has a job type for PowerShell so you schedule / run these kinds of scripts as a SQL Agent job.


Learn more about SQL Server tools