I’ve been working on a recent case – and there have been some in the past that were very similar – where a customer had added users to a custom SharePoint group, then later found those same users were gone! We had an idea what could trigger it but this time we managed to nail the root cause and will be requesting a fix from the product group. But first a quick recap of how Project Server and SharePoint permissions play together – and it isn’t always nicely…
Project Server will control the membership of its own SharePoint groups and also the individual permissions at project site level. Project Server groups can be identified by the name – such as Project Managers Group (Microsoft Project Server). Project uses these groups to give permission to the /PWA site – then for the individual project sites for each project the permissions are set for each user, and the permission level that user has is a Project Server one (such as Team Member (Microsoft Project Server). Worth remembering that permission levels are not the same as groups. Whenever Project needs to synchronize users, which could be after an Active Directory Sync, the editing of a category or group – or the editing of an individual user – then the process is that we remove everyone from the Project groups and directly on the sites then put back the people who should be there. We don’t look to see who was there – we remove EVERYONE. So if you have added someone directly to a site, or added them to a Project managed SharePoint group, then unless they have a right to be there based on permissions and group/category membership they will be removed and not put back when a sync occurs. It is nothing personal – we don’t even look to see who they are.
The exception to this is the use of custom SharePoint groups – or basically a new SharePoint group that Project Server isn’t interested in. If you add a new SharePoint group, and put users into it (regardless of whether they are in Project or not) then they will not be touched when a sync occurs. Worth pointing out that this can give them access to sites – but unless they are in Project Server too they will not be able to get to /PWA.
And now on to the bug…
In some cases we were seeing that users would be removed from groups that we should not have been touching. These were always Project Server users who had been added manually to another custom SharePoint group. Closer examination of what was happening in the background showed that they were not just being removed from the group – but were actually being removed from the site – and so they would be taken out of all groups within that site too!
Following an earlier hunch (Thanks ProjectHosts!) that this was related to some specific Project Sites we were able to identify that the condition was triggered if a project site had been deleted but still existed in the list of sites on the PWA, Server Settings, Project Sites page. Once we had an in-house repro it wasn’t difficult to trace the flow in debug and see what was going on. The issue was that with the non-existent site our code was falling back to the top level – and because it was a top level it was then taking a path that removed the user from the site rather than just the web. At the Project level this didn’t matter – as we would be putting them back again anyway – but we wouldn’t be putting them back in to any custom SharePoint groups, as we were not even aware that we had dropped them. One slight variation that can trigger the same problem is for sites that do actually exist but the site starts with a space – so something like “http://server/PWA/ Site”. Not sure how a site like this can actually get created – possibly it was migrated from 2007 – as the UI tend to stop you doing anything like this.
The workaround is pretty simple – use the Server Settings, Project Sites page, and the Delete Site option for any sites that really don’t exist – or if the leading space is the issue just correct the URL so the site and reference to the site don’t have this leading space. As mentioned earlier we have a fix request in progress but unlikely to see this before the February 2013 Cumulative Update.
If you have lots of sites then it might be tedious to click each one to see if it exists, so a couple of ways to detect the issue. Firstly for the deleted sites a query against the published and content databases will help:
Select PROJ_NAME from ProjectServer_Published.dbo.MSP_PROJECTS
Where WPROJ_ISSUE_LIST_NAME NOT in (Select tp_ID from WSS_Content.dbo.AllLists where tp_DeleteTransactionId = (0x))
Or if like me you would prefer to stay away from the databases then the ULS logs give a clue if you know what to look for. Turn Project Server, Administration to Verbose and you will see plenty of lines like the following as Project Server goes through the sites – filter for EventID of 8sv7 to get rid of noise:
PWA:http://Server/PWA, ServiceApp:Project Server Service Application, User:i:0#.w|domainuser, PSI: UpdateSingleUserMembershipForWssSite: updating user i:0#.w|domainsyncuser. Project Uid – http://server/PWA/Sitename, workspace – a2496ce2-1d47-41d7-b4b5-39c4d7d0d970
However, one of these lines will not show the full URL to the project site but will stop at PWA, such as:
PWA:http://Server/PWA, ServiceApp:Project Server Service Application, User:i:0#.w|domainuser, PSI: UpdateSingleUserMembershipForWssSite: updating user i:0#.w|domainsyncuser. Project Uid – http://Server/PWA, workspace – 0d8259d2-ed93-4508-b442-22f0ec3d3d7f
You’ve probably noticed that we have the Projec Uid and workspace transposed, but the GUID after the word workspace will actually be the Project UID of the project causing the issue. You can identify this by querying the database such as:
Select ProjectName from MSP_EpmProject where ProjectUid = ‘0d8259d2-ed93-4508-b442-22f0ec3d3d7f’
Once you know the name then check out the site in Project Sites and either delete or correct the URL and all will be good.
A quick way to search the logs would be to use your PWA URL and search for “Project Uid – http://Server/PWA,”.
For the adventurous amongst you who may have started troubleshooting and tried tracing the SQL I’ll mention a couple of the stored procedures you may have come across – just so the search engines might also find you the solution – we normally use proc_SecRemoveUserFromSiteGroupByLogin to remove the users from the Project groups, and proc_SecRemoveUserFromScopeByLogin to remove them from a web – but seeing proc_SecRemoveUserFromSite might be an indication that you are running in to this issue (although it would be normal, but not so usual, if the Project site also happened to be a top level site).
This is probably also an issue with 2007 too, but happy to say that 2013 doesn’t appear to suffer from this same problem. In 2013 we can also run purely with SharePoint permissions and without the project groups and categories – or you can use the familiar groups and categories. Take a look at the permission articles under the Technical Reference at http://technet.microsoft.com/en-us/library/cc197638(v=office.15).aspx.
One final point – another common request is that permissions should be given to Project sites based on a users RBS – in the same way that permissions to Projects themselves can be assigned. Unfortunately this isn’t possible and isn’t something we will be changing with 2010.
Thanks to Rob Cason for being my memory on a similar case from summer 2011, and thanks to my SharePoint colleague Daneil Aguiar for excellent troubleshooting that got us moving in the right direction.