Home Contact

The Frog Pond of Technology

Ripples of Knowledge for SharePoint and Other .Net Technologies

News

My blog has moved to WordPress at BrianTJackett.com. Any pages will automatically redirect to the new address in 7 seconds. Please excuse an issues during this migration.

 Subscribe to this blog


About Me

Name:
Brian T. Jackett
Location:
Columbus, OH
Company:
Microsoft

Find me on...

Tag Cloud


Article Categories

Archives

Post Categories

Image Galleries

Syndication:

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


Tuesday, January 4, 2011 3:05 PM

Feedback

# re: SharePoint 2010 PowerShell Script to Find All SPShellAdmins with Database Name

Thank 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. 6/6/2011 11:23 AM | Chris

# re: SharePoint 2010 PowerShell Script to Find All SPShellAdmins with Database Name

Chris,

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.
6/7/2011 11:30 PM | Brian T. Jackett

# re: SharePoint 2010 PowerShell Script to Find All SPShellAdmins with Database Name

Thanks 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. 6/8/2011 8:33 PM | Chris

# re: SharePoint 2010 PowerShell Script to Find All SPShellAdmins with Database Name

Chris,

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.
6/29/2011 10:12 AM | Brian T. Jackett

Post A Comment
Title:
Name:
Email:
Comment:
Verification: