ユーザー情報の抽出-SQL- [技術情報]
今回はユーザー情報の取得について書きます。
SharePointに於いてユーザーに関するテーブルは下記のテーブルです。
・UserInfoテーブル … 過去にサイトコレクションにアクセスしたユーザー
代表的なカラム:tp_siteID,tp_ID,tp_login,tp_title,tp_email
・WebGroups … サイトコレクションに登録されている権限グループ
代表的なカラム:SiteID,WebID,ID,Title
・WebMembersテーブル … サブサイト毎のユーザー一覧
全カラム:WebID,UserID,STSToken
・WebGroupMembershipテーブル … サブサイトとユーザーと権限グループを紐つける
全カラム:WebID,GroupID,MemberID
UserInfoテーブルはSiteIDしか情報を持っていないため、他のテーブルと関連づけて抽出しないとサブサイト(WebID)毎には抽出できないようになっています。
上記のテーブルを組み合わせることによりいろいろな情報を取得することが可能です。
下記に私が運用で使用しているSQLを2つほど例として記載しておきます。
サイトユーザーの取得SQL
SELECT DISTINCT u.tp_Login FROM dbo.UserInfo as u
LEFT JOIN WebMembers as wm on u.tp_id = wm.UserID
INNER JOIN WebGroupMemberShip as wgms on wgms.MemberID = u.tp_id
INNER JOIN WebGroups as wg on wgms.WebID = wg.WebID
and wgms.GroupID = wg.ID
WHERE u.tp_Deleted = 0
アクセス権の取得SQL
SELECT w.FullUrl,u.tp_Login,wg.Title from UserInfo as u
LEFT JOIN WebMembers as wm on u.tp_id = wm.UserID
LEFT JOIN Webs as w on u.tp_SiteID = w.SiteID
LEFT JOIN WebGroupMemberShip as wgms on w.ID = wgms.WebID
and u.tp_id = wgms.MemberID
LEFT join WebGroups as wg on wgms.WebID = wg.WebID
and wgms.GroupID = wg.ID
WHERE wgms.MemberID is not NULL
GROUP BY w.FullUrl,u.tp_Login,wg.Title
ORDER BY FullUrl