Welcome to PowerShell Week #2 for SQL University! One of the frequent questions I hear is, "Who has access to my SQL Server?" Using PowerShell we can answer this question fairly easily. Here's what we need to do:

1. Retrieve the list of Windows logins for a given SQL Server instance.
2. For Windows users, simply output who the Windows user is.
3. For Windows groups we have to get the members of the groups, including nesting.

We'll use Quest's ActiveRoles Server Management Shell PowerShell plugin to accomplish #3, at least with respect to domain groups. With a single cmdlet, we're able to get all the members of a group who are Windows users, even indirectly. The Get-QADGroupMembers cmdlet is our power tool to accomplish this. In order to determine whether a group is a domain group or not, we'll split the name we get back from SQL Server into it's Domain/Machine/BUILTIN and name parts. If we see the name of the computer or we see BUILTIN, we know it's not a domain group. Now in this case we're using $env:ComputerName, which will correspond to the computer name where the script is being executed. If you're going to execute remotely, you'll probably want to rework the script to know what your domains are and look for them instead.

I mentioned that with respect to domain groups we have a great cmdlet to help us out. This is not the case with local groups. Local groups are hard. There's just no getting around that one. We have to delve into ADSI and that limits our options. So with local groups we'll simply settle for outputing the names of the group members. Hopefully you've minimized local groups as much as possible, as the best practice is to use domain level groups. Here's the script:

# You will need Quest's ActiveRoles Management Shell - http://www.quest.com/powershell/activeroles-server.aspx

Add-PSSnapin Quest.ActiveRoles.ADManagement
Add-PSSnapin sqlserverprovidersnapin100
Add-PSSnapin sqlservercmdletsnapin100
$SQLServerInstance = "MySQLServer"
$WindowsCredentials = Invoke-Sqlcmd -ServerInstance $SQLServerInstance -Database master -Query "SELECT name, type FROM sys.server_principals WHERE type IN ('U', 'G');"
ForEach ($Credential in $WindowsCredentials)
{
  if ($Credential.type -eq "U")
  {
    Write-Host "User:", $Credential.name
  } else {
    Write-Host "Group:", $Credential.name
    $Parts = $Credential.name.split("\")
    $Domain = $Parts[0]
    $GroupName = $Parts[1]
    if (($Domain -eq $env:ComputerName) -or ($Domain -eq "BUILTIN"))
    {
        $ADSIObj = [ADSI]"WinNT://$env:ComputerName/$GroupName"
        $LocalGroupMembers = @($ADSIObj.PSBase.Invoke("Members"))
        ForEach ($GroupMember in $LocalGroupMembers)
        {
            Write-Host "  Member:" $GroupMember.GetType().InvokeMember("Name", "GetProperty", $null, $GroupMember, $null)
        }
    } else {
        $AllGroupMembers = Get-QADGroupMember $Credential.name -Type 'user' -Indirect
        ForEach ($GroupMember in $AllGroupMembers)
        {
            Write-Host "  Member: $GroupMember"
        }
    }
  }
}