Add-SPShellAdmin and SPWebApplication.GrantAccessToProcessIdentity. What’s the difference?

TLDR – The Add-SPShellAdmin and SPWebApplication.GrantAccessToProcessIdentity are very similar in what they do, but there are a few key differences:

  1. Add-SPShellAdmin: Should be used for granting admin accounts access to run PowerShell commands against the farm. This grants the account 2 database roles (SharePoint_Shell_Access and SPDataAccess) to the specified content database.
  2. SPWebApplication.GrantAccessToProcessIdentity: Should be used for granting service accounts access to the content database. This grants Full Control User Policy to the Web Application and adds the account to the SPDataAccess role for the specified content database.

OK, if you’re still reading…here’s the longer version:

      • SharePoint_Shell_Access Role:
        • Members of the SharePoint_SHELL_ACCESS role have the execute permission for all stored procedures for the database. In addition, members of this role have the read and write permissions on all of the database tables.
      • SPDataAccess Role:
        • The SPDataAccess role will have the following permissions (SPDataAccess should be used for all object model level access to databases):
        • Note: The SP_DATA_ACCESS role replaces the db_owner role in SharePoint 2013. (From https://technet.microsoft.com/EN-US/library/cc678863.aspx#Section4)
          • Grant EXECUTE or SELECT on all SharePoint stored procedures and functions
          • Grant SELECT on all SharePoint tables
          • Grant EXECUTE on User-defined type where schema is dbo
          • Grant INSERT on AllUserDataJunctions table
          • Grant UPDATE on Sites view
          • Grant UPDATE on UserData view
          • Grant UPDATE on AllUserData table
          • Grant INSERT and DELETE on NameValuePair tables
          • Grant create table permission
  • SPWebApplication.GrantAccessToProcessIdentity (https://msdn.microsoft.com/en-us/library/ee556553.aspx):
    • Used for service accounts requiring elevated access to the content database(s)
    • Most Common Use – Needs to be set for service accounts (If using least privileged) running Excel, PerformancePoint, SSRS, etc.
    • First, this sets a full control User Policy for the Web Application:
    • clip_image004
    • Then, this adds the user to the SPDataAccess role for the specified database(s) for the Web Application:
    • clip_image005

-AJB

Enumerating SharePoint Sites for Office 365 Groups With PowerShell

On 11/8/16 Microsoft made true of announcements from August 2016 and announced that Office 365 Groups with connected SharePoint Online team were now available. Link – https://blogs.office.com/2016/11/08/create-connected-sharepoint-online-team-sites-in-seconds/

The SharePoint admin inside me began to think about managing all of these new site collections/cluttering up my SharePoint Admin Center! The good news (Spoiler Alert)…It doesn’t! One of my friends (Brian Kinsella – https://www.linkedin.com/in/briankinsella) had the same question, so I decided to dig in to figure out how to get at all of these new site collections.

Most of you with an active Office 365/SharePoint Online deployment are probably familiar with the SharePoint Admin center:

SPAdminCenterPNG

I went ahead and created a new Office 365 Group called “TestO365Groups”

To do this I did the following:

  1. Go into the Mail App > (Under Groups) Clicked the + button
  2. Fill out the appropriate information (Name, Description, Privacy, etc.)
  3. This provisioned a site collection at following URL (I checked this by clicking the Files tab within the group) –  https://mod604710.sharepoint.com/sites/testo365groups/

Now onto the concerns above!

I was not able to see this site collection in the SharePoint Admin page and I was not able access Office 365 Groups via SharePoint Online PowerShell cmdlets (Get-SPOSite)

sposites

Exchange PowerShell to the rescue! Here’s what you can do to find all those Office 365 group URL’s:

  1. Fire up Exchange Online PowerShell – https://technet.microsoft.com/en-us/library/jj984289(v=exchg.160).aspx
  2. Open PowerShell as Admin and connect to the O365 tenant using the following PowerShell:
Run the following command to get each Office 365 group and its associated SharePoint site URL:
Check it out (There’s a lot of properties you can work into this script if you’d like..109 to be exact):

o365groupsposh

-AJB

Remove-SPWebApplicationAppDomain..You’re Killing Me

After March 2013 PU was released for SharePoint 2013, new PowerShell cmdlets were brought into this world for setting up the SharePoint App Store. I’m a fan, but noticed something that I’d like to share. Read more about these cmdlets here: https://technet.microsoft.com/en-us/library/Dn144963.aspx

Now take a look at the screenshot and let me know what’s wrong:

RemoveSPWebAppAppDomain

OK, here’s what is up. The cmdlet New-SPWebApplicationAppDomain created a new binding for Port 80 for the WebApplication specified. This is because of the way SharePoint Apps work..they don’t play well with host headers..so instead of setting up a new web app listening on Port 80 you can have SharePoint add this binding for you. There may be a circumstance down the road where you decide you want to have a site on port 80 and decide to remove the App Domain using Remove-SPWebApplicationAppDomain. You take a look in IIS..no more Port 80 binding. BUUUUT when you go to create the new web app you get thrown an error message saying “The IIS Web Site you have selected is in use by SharePoint. You must select another port or hostname.” BWOMP

I can run remove/add-spwebapplicationappdomain commands all day and it’ll create and delete the binding in IIS but running the PowerShell commands above you can see that even though IIS is cleaned up, the config database still sees that binding.

At this time I haven’t found a great fix..but here’s what I got (I have tested this on multiple SharePoint 2013 servers..the latest being one with September 2014 CU):

Change the port using PowerShell.

Example:

Since this is affecting the Default zone only you could extend the web app to a temp URL, delete the Default Zone, and extend the original URL back to the default zone, and delete the temp URL zone. I go through this procedure because of custom solutions deployed to the farm. If you delete all zones, things just don’t seem to work right.

SharePoint – Web Application Creation Timeout Annoyance

When trying to create a web application using Central Admin sometimes it will just timeout. This can usually be remedied by either adding more resources on the SharePoint server hosting Central Admin or increasing the Shutdown timeout limit for the Central Admin IIS Application Pool (Usually 300-400 does the trick. See this post: http://www.andrewjbillings.com/sharepoint-2013-web-applications-not-creating-successfully-not-deploying-all-files-to-the-virtual-directory/).

There’s got to be a better way right? Yes, instead of mucking around with timeouts and adding resources for a task you rarely do (Though if this is happening and things are slow..You might want to look at Microsoft’s hardware recommendations and if you are abiding by them).

Any who…PowerShell wins again. There is a ProvisionGlobally() Method for SPWebApplication. This will push all of the items missed at the time of provisioning out to ALL servers running the SharePoint Web Service. Only want to do it to one server? Log on to the local server and run Provision()

More about this lovely time-saver: https://msdn.microsoft.com/en-us/library/microsoft.sharepoint.administration.spwebapplication.provisionglobally.aspx

Example:

-AJB

SharePoint 2010 – Office Web Apps Cache Expiration

I was doing some browsing around a SharePoitn 2010/Office Web Apps-Enabled farm the other day and noticed something interesting that I thought I’d share. By default when you setup Office Web Apps in SharePoint 2010 the cache expiration does not get set in the web app’s property bag.

I started by checking the web app cache URL’s propery bag items. As you can see there is nothing related to cache expiration:

After this I went ahead and set the max size/cache expiration for the Office Web Apps cache URL and then checked the property bag items:

Notice the waccacheexpirationperiod entry now!

 

-AJB

Document Inventory PowerShell Script

The other day I had a request to get a list of all documents in a web application and some information about each document. From here I was able to open the file in Excel and create a PivotTable of each site collection, site, and library, and get counts of the versions and unique permissions for each. In this case a few of the libraries were approaching the 50,000 unique security scope limit in a list/library…be careful of that limit (Described here: http://technet.microsoft.com/en-us/library/cc262787(v=office.15).aspx#ListLibrary)! Here is the script:

Additional Notes:

Site Owners PowerShell Script – Kind Of

I was asked to come up with a script to generate the site owners for all the sites in a specific web application recently. I didn’t have much time to put anything together too fancy so I came up with this script. It looks at each SPWeb object and determents the users in the associated owner group for each SPWeb (SharePoint sub site).  This doesn’t take into account anyone who may have been granted full control explicitly and the $web.AssociatedOwnerGroup may or may not be correct..In this case it was. Hopefully this helps you out..or even provides a starting point to what you are trying to report on.

 

Additional Notes:

  • This script causes additional overhead on the SharePoint farm and should be ran in off hours. This sends a lot of requests to the database
  • Copy everything from function to the last “}” to load the function. Once the function is loaded you can run it using the last line of the script “GetSiteOwnersReport”
  • This does NOT take into account unique permissions. It only looks at the AssociatedOwnerGroup members for each SPWeb object

Cannot Activate Site Feature for SharePoint Publishing – Dang that pages library!

They other day I went to activate the Site (SPWeb) level publishing feature and I ran into an error. I tracked it down in the ULS Logs and found this entry:

08/13/2014 11:15:04.24 w3wp.exe (0x4D94) 0x4E50 SharePoint Foundation General 75fg High Exception was thrown while ensuring dependencies met for feature ‘PublishingWeb’ (id: 94c94ca6-b32f-4da9-a9e3-1f3d343d7ecb): Microsoft.SharePoint.SPException: List does not exist. The page you selected contains a list that does not exist. It may have been deleted by another user. —> System.Runtime.InteropServices.COMException (0x81020026): List does not exist. The page you selected contains a list that does not exist. It may have been deleted by another user. at Microsoft.SharePoint.Library.SPRequestInternalClass.GetListsWithCallback(String bstrUrl

I did some digging and found 2 good links on the matter:

The following script worked for me. Note: I have ran into this issue multiple times and the main culprit is that the Pages library (Or another publishing library) is already created when you go to enable the SPWeb SharePoint Publishing Feature. You could use PowerShell or SharePoint Manager to delete those lists. Or even to update the pages library for the SPWeb object, but this script is much easier 🙂

Also, there was one instance where this script did not work and I needed to run the Enable-SPFeature cmdlet and use the -Force parameter. Once I did this I ran the script below and then everything started working.

 

 Update (10/29/14) – I have ran into this issue multiple times in the same environment now. Even though the above scripts fix the feature activation, the easiest fix has been to just deactivate and reactivate the SharePoint Publishing Infrastructure Site Collection Feature. This company is using a custom master page which needed some fixing up (Reactivating the site collection feature seemed to make the necessary changes).

SharePoint PowerShell Tip – Save a deployed farm solution

Ever deployed a SharePoint farm solution and then lose the WSP file? Good news – there is PowerShell to grab that WSP file and save it off somewhere for DR purposes.

SharePoint 2013 Foundation – Creating the Search Service with PowerShell and Removing those Pesky GUIDs

I found this awesome PowerShell script on Gary LaPointe’s blog and decided to give it a try. This essentially mimics the SharePoint Configuration Wizard, but it gives you the power to use PowerShell! Below are is my experience with this script and how I went about removing the GUIDs from the database names.

Note: This bases the DB Server off of the default DB Server specified in Central Admin (Can be change using PowerShell Later) and it results in databases with GUIDs at the end, but we’ll remove those later :). Obviously change the Managed Account and App Pool names to fit your environment.

This will result in a default topology, but there are GUIDs..yuck! If GUIDs are unacceptable there is a method of renaming Search Service Databases on http://technet.microsoft.com/en-us/library/jj219654.aspx. I went ahead and tried this out.

1. Run the following commands to suspend the search service

2. Now Go into SQL Server Management Studio and set each Search DB to Read-Only Mode (Accept the message to close existing connections). Right Click Database | Properties | Options. Set Read-Only to True

3. Perform a copy-only backup of each Search Database

4. Detach all of the old databases

5. Restore each database backup (Change Restore to File Names so there are no GUIDS in the MDF and LDF files)

6. Right Click Each Database and Rename (Had a pain with Admin DB. I ended up detaching and reattaching with new name)

7. Restore the old databases (Delete MDF and LDF Files first! May need to close out SQL Mgmt Studio).

8. Use PowerShell to point the Search Service Application at the renamed databases

9. Delete the old Databases with GUIDs

Check it out!
clip_image002

 

Edit: As Tuppence pointed out below there is now a way to set this up from the get-go. Check out this link: http://www.funwithsharepoint.com/provision-search-for-sharepoint-foundation-2013-using-powershell-with-clean-db-names/