Problem

    Yesterday on Twitter my friend @cacallahan asked for some help on how she could get all SharePoint 2010 SPShellAdmin users and the associated database name.  I spent a few minutes and wrote up a script that gets this information and decided I’d post it here for others to enjoy.

Background

    The Get-SPShellAdmin commandlet returns a listing of SPShellAdmins for the given database Id you pass in, or the farm configuration database by default.  For those unfamiliar, SPShellAdmin access is necessary for non-admin users to run PowerShell commands against a SharePoint 2010 farm (content and configuration databases specifically).  Click here to read an excellent guest post article my friend John Ferringer (twitter) wrote on the Hey Scripting Guy! blog regarding granting SPShellAdmin access.

 Solution

    Below is the script I wrote (formatted for space and to include comments) to provide the information needed.

Click here to download the script.

# declare a hashtable to store results

$results = @{}

# fetch databases (only configuration and content DBs are needed)

$databasesToQuery = Get-SPDatabase |

    Where {$_.Type -eq 'Configuration Database' -or $_.Type -eq 'Content Database'}

# for each database get spshelladmins and add db name and username to result

$databasesToQuery |

    ForEach-Object {$dbName = $_.Name;

                    Get-SPShellAdmin -database $_.id |

                        ForEach-Object {$results.Add($dbName, $_.username)}}

# sort results by db name and pipe to table with auto sizing of col width

$results.GetEnumerator() | Sort-Object -Property Name | ft -AutoSize

Conclusion

    In this post I provided a script that outputs all of the SPShellAdmin users and the associated database names in a SharePoint 2010 farm.  Funny enough it actually took me longer to boot up my dev VM and PowerShell (~3 mins) than it did to write the first working draft of the script (~2 mins).  Feel free to use this script and modify as needed, just be sure to give credit back to the original author.  Let me know if you have any questions or comments.  Enjoy!

      -Frog Out

Links

PowerShell Hashtables

http://technet.microsoft.com/en-us/library/ee692803.aspx

SPShellAdmin Access Explained

http://blogs.technet.com/b/heyscriptingguy/archive/2010/07/06/hey-scripting-guy-tell-me-about-permissions-for-using-windows-powershell-2-0-cmdlets-with-sharepoint-2010.aspx