SharePoint Permissions and Business Portal

I don't know if anyone else has the same headache when setting up Business Portal permissions, but I invariably miss something between the SharePoint permissions and the Business Portal permissions. Or the customer needs to change some access around from time to time.

Either it's not there or I've just missed it; I'm no expert in SharePoint by any means, so it could be either. With Active Directory, you just go to a user's "Member Of" tab and you see a list of the groups in which they are a member. In SharePoint, you can see a list of users in a group, but not not a list of groups for a user.

Another problem I fall into is forgetting to assign a user to the "BP Internal User" group.

Here are two simple and related scripts to help with both those problems. The first is to see which groups users are in. The second is to make sure all users are in the "BP Internal User" group.

(connecting to the SharePoint content database)

SELECT 
      tp_Title [User],
      title [Group]
FROM
      UserInfo U
LEFT JOIN GroupMembership GM
      ON U.tp_ID = GM.memberid
LEFT JOIN Groups G
      ON GM.groupid = G.id
 
-- WHERE tp_Title = 'some user'
ORDER BY
      [User],
      [Group]

SELECT 
      tp_Title [User], 
      title [Group]
FROM
      UserInfo U
LEFT JOIN (GroupMembership GM
      INNER JOIN Groups G
            ON GM.groupid = G.id
            AND G.title = 'BP Internal User'
      ) ON U.tp_ID = GM.memberid
ORDER BY
      [User]