SQL University: Who has access to my SQL Server?

Brian Kelley, 2011-04-26

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”

}

}

}

}

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads