Find user permissions assigned in one or more site collections of all items

MOSS does not provide out-of-box interface to view assigned permissions to the same user cross site collections. Although there are tools to help you, no matter it's paid or free, if your environment does not allow free tools and you have to find it for now, what can you do?

Since everything is stored in SQL database, why not write some SQL statements to find them? That's what I have been doing before we have a nice tool to use. If you already know the structure of tables used by MOSS, then you may be able to do it in a short period of time.

If not, here is the SQL query you can use to do it:

Select 'http://yourportal/'+a.scopeurl as ObjectURL, a.title as UserID,roles.title as Permission,a.SPgroup, a.ADLogin
from roles inner join
(
select perms.scopeid, perms.scopeurl, perms.roledefwebid, alluser.title, alluser.ADLogin,alluser.SPgroup,roleassignment.roleid, roleassignment.siteid
from perms inner join
roleassignment on
perms.siteid=roleassignment.siteid
and
perms.scopeid=roleassignment.scopeid
inner join(
(
select groups.siteid, groups.id as id,userinfo.tp_login as ADLogin,userinfo.tp_title as title, groups.title as SPGroup
from groups inner join
groupmembership on groups.siteid=groupmembership.siteid
and
groups.id=groupmembership.groupid
inner join userinfo on
groupmembership.siteid=userinfo.tp_siteid
and
groupmembership.memberid=userinfo.tp_id)

union

select tp_siteid as siteid, tp_id as id,userinfo.tp_login as ADLogin, tp_title as title, 'no' as SPGroup from userinfo) as Alluser
on
roleassignment.siteid=alluser.siteid
and
roleassignment.principalid=alluser.id) as a
on
roles.siteid=a.siteid
and
roles.webid=a.roledefwebid
and
roles.roleid=a.roleid

Let me explain the columns you get:

1. frist colunmn is the whole URL of an object (site, , sub site, library/list, folder oritem) which has unique permission
2. The User ID
3. Permission assigned to this user.
4. SharePoint User Group this user is assigned to. If the permission assigned to this user individually, this column shows No.
5. User log in ID.

So, this query will tell you in a single content database, what are those objects have unique permissions, who has permission, what type permission assigned, and this permission assigned to a user group or individually.

Enjoy it.