<Update 2011-29-06: Modify script to handle multiple admins per database>
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 (updated script to handle multiple admins per database and sort them alphabetically).
# declare an array to store results
$results = @()
# fetch databases (only configuration and content DBs are needed)
$databasesToQuery = Get-SPDatabase |
Where-Object {$_.Type -eq 'Configuration Database' -or $_.Type -eq 'Content Database'} |
Sort-Object –Property Name
# for each database get spshelladmins and add db name and username to result
$databasesToQuery |
ForEach-Object {$dbName = $_.Name;
Get-SPShellAdmin -database $_.id |
Sort-Object $_.username |
ForEach-Object {$results += @{$dbName=$($_.username)}}}
# sort results by db name and pipe to table with auto sizing of col width
$results.GetEnumerator() | 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
The Frog Pond of Technology 
Originally posted on: https://briantjackett.com/archive/2011/01/04/sharepoint-2010-powershell-script-to-find-all-spshelladmins-with-database.aspx#581072Thank you for providing this script. One thing that I noticed when using it, though, is that if there are multiple users/groups per database, I get an error about the key already being added via the $results.Add method. Therefore, any entries beyond the first do not display in the results.Obviously, this is due to the use of the hash table for storing the information (requires a unique key value), but I don’t have the know-how at this time to quickly adjust the script to account for duplicate keys.I just wanted to share this observation.
LikeLike
Originally posted on: https://briantjackett.com/archive/2011/01/04/sharepoint-2010-powershell-script-to-find-all-spshelladmins-with-database.aspx#581209Chris, Very good point about unique keys and hashtables that I didn’t test thoroughly for. There are numerous other solutions. Instead of adding the results to a hashtable one thought would be to pipe to “select-object -property $dbName,$_.username” instead. I don’t have my SP2010 VM running right now but I’ll try to verify that soon and update the post with a better solution. Thanks for pointing out my oversight. Let me know if you find an alternate solution.
LikeLike
Originally posted on: https://briantjackett.com/archive/2011/01/04/sharepoint-2010-powershell-script-to-find-all-spshelladmins-with-database.aspx#581344Thanks for the prompt reply! I’m still learning PowerShell, so I’ve not had much success tweaking your script for my needs or based on your feedback. I do appreciate you offering to take a look, though, and I’ll check back periodically to see if you come up with a solution.
LikeLike
Originally posted on: https://briantjackett.com/archive/2011/01/04/sharepoint-2010-powershell-script-to-find-all-spshelladmins-with-database.aspx#583826Chris, Sorry for the long delay. I was gone on vacation for over a week and also moving into a new condo. I finally got to updating the blog post and posting a new script to handle multiple admins per database. The trick was to use an array “$array = @()” and add individual hashtables to the array. Bit of a workaround to allow non-unique keys, but it appears to work. Try it out and let me know if you have any issues. Thanks again for catching my bug.
LikeLike