SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL University: Who has access to my SQL Server?

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"

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.


Posted by Fabrizio Faleni on 29 April 2011

Dear Brian,

Thanks a lot for sharing your knowledge wit us: your PS script is nice and clean, it teaches us how to query a database and how to use the resulting dataset and how to search AD for info.

If I may criticise a little, I would have expected more Info o the SQL permissions side: I mean, it is nice to know the logins but it will be of little help for everyday work or for an audit because the most difficult part is getting info on the associated user roles and rights on each object. I mean, your pill is a very nice idea to start something more complex.

Leave a Comment

Please register or log in to leave a comment.