SharePoint 2010 PowerShell Script to Find All SPShellAdmins with Database Name

<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.

Twitter q1Twitter q2

 

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

ScriptRun1

 

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

4 thoughts on “SharePoint 2010 PowerShell Script to Find All SPShellAdmins with Database Name

  1. 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.

    Like

  2. 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.

    Like

  3. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s