SharePoint 2007 - Check A User's Permissions

This request always comes up on every job. "We need to make sure that sensitive data is not being accessed by the wrong person - Can you check out UserXYZ and see what permissions they have?" There really is no way to do this out of the box with SharePoint. So, I wrote a couple queries that will display all permissions a User has for Scope and Leaf levels. Just replace the 'login ID' in lines 24 and 28 with the user(s) you are looking for. In addition, you can change the WHERE U.tp_login to a WHERE U.tp_login IN to check mutiple login ID's. Just list the array of login ID's inside of () separated by commas - eg: WHERE U.tp_login IN (loginID1, loginID2, loginID3).

Scope Level User’s Permissions

-- This sql will show a user's permissions for all scope level objects

USE MSContent01DB     -- change to database name you need
SELECT DISTINCT 	U.tp_login user_login, 
			U.tp_title user_title, 
			W.title site_title, 
			W.fullUrl site_full_url, 
			P.scopeurl scope_url, 
			R.title access_level 
FROM			RoleAssignment A
			JOIN Roles R
			ON		R.siteID = A.siteID 
					AND R.roleID = A.roleID 
			JOIN UserInfo U 
			ON		U.tp_siteID = A.siteID 
					AND U.tp_id = A.principalID 
			JOIN Sites S
			ON		S.[id] = A.siteID 
			JOIN Perms P
			ON		P.siteID = A.siteID 
					AND P.scopeID = A.scopeID 
			JOIN Webs W	
			ON		W.[id] = P.webID 
WHERE			U.tp_login = '' -- make sure to use single quotes for string

ORDER BY 		U.tp_login, 
			W.title, 
			W.fullurl, 	
			P.scopeurl

Leaf Level User’s Permissions

-- This sql will show a user's permissions for all leaf level objects

USE MSContent01DB     -- change to database name you need
SELECT DISTINCT U.tp_login identity_name,
                U.tp_title user_title,
                W.title site_title,
                W.fullUrl site_full_url,
                A.DirName dir_name,
                A.LeafName leaf_name,
                R.title access_level

FROM            RoleAssignment O
                JOIN AllDocs A
                ON              A.SiteId  = O.siteID
                            AND A.ScopeId = O.scopeID
                JOIN Roles R
                ON              R.siteID = O.siteID
                            AND R.WebId  = A.WebId
                            AND R.roleID = O.roleID
                JOIN UserInfo U
                ON              U.tp_siteID = O.siteID
                            AND U.tp_id     = O.principalID
                JOIN Sites S
                ON              S.[id] = O.siteID
                JOIN Webs W
                ON              W.[id] = A.WebId

WHERE			U.tp_login = '' -- make sure to use single quotes for string

ORDER BY        identity_name,
                site_title,
                site_full_url,
                access_level


Categories: SharePoint 2007
Permalink | Comments (0) | Post RSSRSS comment feed

Comments